J'ai écrit la fonction ci-dessous que je voudrais utiliser dynamiquement dans ma feuille Excel où la fonction évaluerait la valeur de la variable sélectionnée dans la formule et, en fonction de cette valeur, effectuerait un simple calcul référençant les valeurs dans les cellules de des colonnes différentes mais dans la même ligne.
Je sais que le code ci-dessous fonctionne, mais comment puis-je réécrire les plages pour que ma fonction puisse être utilisée dynamiquement pour chaque ligne de la même colonne?
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
5 Réponses :
Vous pouvez ajouter une entrée pour la ligne, puis concaténer la ligne avec vos chaînes de plage. Comme ça:
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
Vous pouvez également utiliser Cells pour accéder à ces plages au lieu de Range . Cells vous permet de sélectionner la ligne et la colonne comme deux arguments distincts. C'est une chose extrêmement utile à savoir si vous allez faire plus de VBA à l'avenir.
Eh bien, le moyen simple et rapide serait de passer la ligne comme argument à la fonction.
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
Magnifique! Merci!!
Est-ce que cela doit être VBA? Cette formule seule solution devrait fonctionner pour vous:
=$H3*0.001*$K3*INDEX($N3:$R3,,A1)
Le dernier 1 est l'index de la colonne dans cette plage ( N: R ) que tu veux. Donc 1 = "Utiliser la colonne N" et 2 = "Utiliser la colonne O" etc. Vous pouvez simplement copier la formule et coller les lignes vers le bas et cela ajustera automatiquement la ligne les nombres tout en gardant les colonnes identiques.
De plus, si vous préférez, vous pouvez utiliser une référence de cellule au lieu de coder en dur le nombre "Colonne sélectionnée / TabSize", comme ceci (où le "Selected Column / TabSize "numéro dans la cellule A1 dans cet exemple):
=$H3*0.001*$K3*INDEX($N3:$R3,,1)
C'est très utile! Merci!
Vous pouvez utiliser Application.Caller pour détecter la cellule qui exécute la fonction:
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
Alternativement, si TabSize est uniforme de la manière suggérée par votre question - vous pouvez également 'calculer' lequel colonne de celui-ci:
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
Pourriez-vous expliquer l'utilisation des parenthèses dans la ligne 'AddedValue2'. Je veux dire, est-ce une sorte d'astuce pour éviter un débordement?
Non, rien d'aussi intelligent que j'ai peur @ VBasic2008. Ils sont toujours là car ils sont dans l'exemple de code fourni par l'OP et je trouve que laisser autant de structure originale dans une réponse que possible aide l'OP à la comparer à leur code.
D'accord, je pense que je commence à comprendre la deuxième solution en particulier. Merci!
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
La fonction sera utilisée par exemple: = Valeur ajoutée (K3) dans, par exemple, la cellule L3 de sorte que lorsque l'utilisateur entre un entier (un nombre pair) dans la cellule K3, un montant en dollars apparaîtra dans la cellule L3 qui correspondrait à laquelle même numéro 2-10 que l'utilisateur entre. Je voudrais utiliser cette fonction pour chaque cellule d'une colonne de cette façon