Dear vba masters,
I'm requesting some help transforming my index multiple match excel formula into a vba procedure.
I have been able to start with a vlookup but as i have to loop through rows and columns it won't be sufficient.
In the calcul sheet, i need to find the result in the sheet resultats corresponding to the col C3 and the row 2, then with that result find the corresponding model that is in the row 1 between the sheets calcul and prime par modele.
This will give an amount that i want to multiply by the number of models found in the sheet data.
Below my code so far but i ended with a double vlookup and only able to loop in the first column, i need to do it for all columns until "HT"
and also the excel formula that gets me the result i need in vba
Excel formula
=INDEX('Prime par modèle'!$G$2:$BA$26;MATCH(INDEX(Resultats!$A$1:$GB$99999;MATCH(Calcul!$C3;Resultats!$C$1:$C$99999;0);MATCH(F$2;Resultats!$A$1:$GB$1;0));'Prime par modèle'!$F$2:$F$31;0);MATCH(Calcul!F$1;'Prime par modèle'!$G$1:$BA$1;0))*COUNTIFS(data!$I$2:$I$100000;F$1;data!$L$2:$L$100000;$D3;data!$AU$2:$AU$100000;$C3;data!$AF$2:$AF$100000;0))
VBA code
Public Sub Prime()
Dim data As Worksheet, modele As Worksheet, Calcul As Worksheet, Resultats As Worksheet
Set data = ThisWorkbook.Worksheets("data")
Set modele = ThisWorkbook.Worksheets("Prime par modèle")
Set Calcul = ThisWorkbook.Worksheets("Calcul")
Set Resultats = ThisWorkbook.Worksheets("Resultats")
Dim datamodele As Range
Dim last_row_Calcul As Long, last_row_data As Long, last_row_Resultat As Long, x As Long
last_row_Calcul = Calcul.Range("A" & Rows.Count).End(xlUp).Row
last_row_data = data.Range("A" & Rows.Count).End(xlUp).Row
last_row_Resultat = Resultats.Range("A" & Rows.Count).End(xlUp).Row
Set datamodele = modele.Range("F2:BA26")
Set datadata = data.Range("F1:AU" & last_row_data)
Set dataresultat = Resultats.Range("C2:E" & last_row_Resultat)
For x = 3 To last_row_Calcul
On Error Resume Next
Calcul.Range("e" & x).Value = Application.WorksheetFunction.VLookup( _
Application.WorksheetFunction.VLookup( _
Calcul.Range("C" & x).Value, dataresultat, 3, False), datamodele, 6, False)
Next x
End Sub
Display More
Many thanks in advance for any help provided !!