Posts by olight

    I'm not a big fan of tables in excel, i think we loose flexibility with it..

    But i will give a try

    now i'm still looking for a vba solution because i'm stuck on this for 2 days already and i would like to see the correct way to do it so if you have any advice on the vba code :)

    Hello Roy,

    Thanks for your advice, i will be carefull with the codes lines i post in the future.

    I wanted to find a vba solution to avoid haing hundreds of formulas that lower the speed of my file.

    I would like also to give that file to other users so they just have to push the button to make the calculations

    Don't you think it would be better ?

    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

    Many thanks in advance for any help provided !!