Index and Match Function in VB

[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

 

[Bob Phillips]

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

 

[daniroy]

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

 

[Bruno Campanini]

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

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

 

[Bob Phillips]

It's only temporary Dani, it gets cleared out again.

 

[Don Guillett]

You could probably use FIND and OFFSET to do this.