Ich habe die folgende Funktion geschrieben, die ich dynamisch in meinem Excel-Blatt verwenden möchte, wobei die Funktion den Wert der in der Formel ausgewählten Variablen bewertet und basierend auf diesem Wert eine einfache Berechnung durchführt, die auf Werte in Zellen in verschiedenen Spalten verweist in der gleichen Reihe.

Ich weiß, dass der folgende Code funktioniert, aber wie kann ich die Bereiche neu schreiben, damit meine Funktion für jede Zeile derselben Spalte dynamisch verwendet werden kann?

Function AddedValue(TabSize As Integer)

Select Case TabSize

    Case 2
        AddedValue = Range("K3") * (Range("N3") * (Range("H3") * 0.001))

    Case 4
        AddedValue = Range("K3") * (Range("O3") * (Range("H3") * 0.001))

    Case 6
        AddedValue = Range("K3") * (Range("P3") * (Range("H3") * 0.001))

    Case 8
        AddedValue = Range("K3") * (Range("Q3") * (Range("H3") * 0.001))

    Case 10
        AddedValue = Range("K3") * (Range("R3") * (Range("H3") * 0.001))

End Select
End Function
1
eerie_valeerie 18 Jän. 2019 im 18:10

5 Antworten

Beste Antwort

Sie können eine Eingabe für die Zeile hinzufügen und die Zeile dann mit Ihren Bereichszeichenfolgen verknüpfen. Wie so:

Function AddedValue(TabSize As Integer, iRow as Long)

Select Case TabSize

    Case 2
        AddedValue = Range("K" & iRow) * (Range("N" & iRow) * (Range("H" & iRow) * 0.001))

    Case 4
        AddedValue = Range("K" & iRow) * (Range("O" & iRow) * (Range("H" & iRow) * 0.001))

    Case 6
        AddedValue = Range("K" & iRow) * (Range("P" & iRow) * (Range("H" & iRow) * 0.001))

    Case 8
        AddedValue = Range("K" & iRow) * (Range("Q" & iRow) * (Range("H" & iRow) * 0.001))

    Case 10
        AddedValue = Range("K" & iRow) * (Range("R" & iRow) * (Range("H" & iRow) * 0.001))

End Select
End Function
0
DavidN 18 Jän. 2019 im 15:24

Mit Application.Caller können Sie die Zelle erkennen, in der die Funktion ausgeführt wird:

Public Function AddedValue(TabSize As Integer)
    Application.Volatile
    r = Application.Caller.Row
    With Application.Caller.Parent.Rows(r)
        firstcell = .Cells(1, 11) ' column K
        Select Case TabSize
            Case 2
                secondcell = .Cells(1, 14) ' column N
            Case 4
                secondcell = .Cells(1, 15) ' column O
            Case 6
                secondcell = .Cells(1, 16) ' column P
            Case 8
                secondcell = .Cells(1, 17) ' column Q
            Case 10
                secondcell = .Cells(1, 18) ' column R
            End Select
        thirdcell = .Cells(8) ' column H
        AddedValue = firstcell * (secondcell * (thirdcell * 0.001))
    End With
End Function

Wenn TabSize in der von Ihrer Frage vorgeschlagenen Weise einheitlich ist, können Sie auch berechnen, welche Spalte daraus:

Public Function AddedValue2(TabSize As Integer)
    Application.Volatile
    r = Application.Caller.Row
    With Application.Caller.Parent.Rows(r)
        firstcell = .Cells(1, 11) ' column K
        secondcell = .Cells(1, 13 + (TabSize / 2)) ' dynamic column from TabSize
        thirdcell = .Cells(8) ' column H
        AddedValue2 = firstcell * (secondcell * (thirdcell * 0.001))
    End With
End Function
2
CLR 18 Jän. 2019 im 15:35

Nun, der schnelle und einfache Weg wäre, die Zeile als Argument an die Funktion zu übergeben.

Function AddedValue(TabSize As Integer, row As Long)

Select Case TabSize
    Case 2
        AddedValue = Range("K" & row) * (Range("N" & row) * (Range("H" & row) * 0.001))
    Case 4
        AddedValue = Range("K" & row) * (Range("O" & row) * (Range("H" & row) * 0.001))
    Case 6
        AddedValue = Range("K" & row) * (Range("P" & row) * (Range("H" & row) * 0.001))
    Case 8
        AddedValue = Range("K" & row) * (Range("Q" & row) * (Range("H" & row) * 0.001))
    Case 10
        AddedValue = Range("K" & row) * (Range("R" & row) * (Range("H" & row) * 0.001))
End Select
End Function
0
Kubie 18 Jän. 2019 im 15:24

Muss das VBA sein? Diese Lösung nur für Formeln sollte für Sie funktionieren:

=$H3*0.001*$K3*INDEX($N3:$R3,,1)

Das letzte 1 ist der Index der Spalte in dem Bereich (N:R), den Sie möchten. Also 1 = "Spalte N verwenden" und 2 = "Spalte O verwenden" usw. Sie können einfach die Formel kopieren und Zeilen einfügen, und die Zeilennummern werden automatisch angepasst, während die Spalten beibehalten werden gleich.

Falls gewünscht, können Sie außerdem eine Zellreferenz verwenden, anstatt die Nummer "Ausgewählte Spalte / TabSize" wie folgt fest zu codieren (wobei die Nummer "Ausgewählte Spalte / TabSize" in Zelle A1 in diesem Beispiel):

=$H3*0.001*$K3*INDEX($N3:$R3,,A1)
0
tigeravatar 18 Jän. 2019 im 15:28

Dynamische Zellberechnung

Option Explicit

Function AddedValue(TabSize As Long, row As Long) As Double

    Const cValue As String = "2,4,6,8,10"
    Const cStr1 As String = "K"
    Const cStr2 As String = "N,O,P,Q,R"
    Const cStr3 As String = "H"
    Const cDbl1 As Double = 0.001

    Dim vntV As Variant
    Dim vnt2 As Variant
    Dim i As Long

    vntV = Split(cValue, ",")
    vnt2 = Split(cStr2, ",")

    For i = 0 To UBound(vntV)
        If TabSize = CLng(Trim(vntV(i))) Then
            AddedValue = Range(cStr1 & row) * Range(Trim(vnt2(i)) & row) _
                    * Range(cStr3 & row) * cDbl1
            Exit For
        End If
    Next

End Function
0
VBasic2008 18 Jän. 2019 im 16:12