Index and Match Function in VB

  • Thread starter Thread starter daniroy
  • Start date Start date
D

daniroy

A few days ago, I did receive very good help from Max and Dave to
achieve the following formula,

=IF(B5="Synth",INDEX(AUTOS!$K$10:$K$500,MATCH(1,(Autos!$F$10:$F$500='ID'!D5)*(Autos!$G$10:$G$400='ID'!$R$2)*(AUTOS!$I$10:$I$400="C"),0))-INDEX(AUTOS!$K$10:$K$500,MATCH(1,(AUTOS!$F$10:$F$500='ID'!D5)*(AUTOS!$G$10:$G$400='ID'!$R$2)*(AUTOS!$I$10:$I$400="P"),0))+$R$2-$R$5,VLOOKUP(D5,$P$4:$R$8,3)-$R$5)

Basically I was looking to retrieve data based on multiple criteria and
the formula is working just fine.

I now want to also be able to define these results in VBA and store
them as Public Variables.

Unfortunately, and everybody understand my VBA Level is sub standard,
Index or Match function cannot be use the same way in VBA. And I do not
understand which way should I use them BTW ...

Any suggestions?

Regards
Daniel
 
One simple way

Range("IV13").Formula = "=IF(B5=""Synth"",INDEX(AUTOS!$K$10:$K$500," & _
"MATCH(1,(Autos!$F$10:$F$500='ID'!D5)*" & _
"(Autos!$G$10:$G$400='ID'!$R$2)*" & _
"(AUTOS!$I$10:$I$400=""C""),0))-" & _
"INDEX(AUTOS!$K$10:$K$500," & _
"MATCH(1,(AUTOS!$F$10:$F$500='ID'!D5)*" & _
"(AUTOS!$G$10:$G$400='ID'!$R$2)*" & _
"(AUTOS!$I$10:$I$400=""P""),0))+$R$2-$R$5," & _
"VLOOKUP(D5,$P$4:$R$8,3)-$R$5)"
myVar = Range("IV13").Value
Range("IV13").ClearContents
 
Hello Bob,

it works, but ideally I did not wanted to input the value into the
spreadsheet but only store it in VB. But it is fine, I can do it this
way.

Regards
DR
 
A few days ago, I did receive very good help from Max and Dave to
achieve the following formula,

=IF(B5="Synth",INDEX(AUTOS!$K$10:$K$500,MATCH(1,(Autos!$F$10:$F$500='ID'!D5)*(Autos!$G$10:$G$400='ID'!$R$2)*(AUTOS!$I$10:$I$400="C"),0))-INDEX(AUTOS!$K$10:$K$500,MATCH(1,(AUTOS!$F$10:$F$500='ID'!D5)*(AUTOS!$G$10:$G$400='ID'!$R$2)*(AUTOS!$I$10:$I$400="P"),0))+$R$2-$R$5,VLOOKUP(D5,$P$4:$R$8,3)-$R$5)

Basically I was looking to retrieve data based on multiple criteria and
the formula is working just fine.

I now want to also be able to define these results in VBA and store
them as Public Variables.

Unfortunately, and everybody understand my VBA Level is sub standard,
Index or Match function cannot be use the same way in VBA. And I do not
understand which way should I use them BTW ...

Any suggestions?

Regards
Daniel
Click to expand...

You've got to write the formula via VBA.
If you want to have VBA to make what the formula does,
try to explain on detail the operations involved.

Bruno
 
Back
Top