Index and Match Function in VB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

daniroy@gmail.com - 14 Sep 2005 10:44 GMT

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

Reply to this Message

Bob Phillips - 14 Sep 2005 11:30 GMT

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

Signature

HTH

Bob Phillips

> 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.
[quoted text clipped - 10 lines]
> Regards
> Daniel

Reply to this Message

daniroy@gmail.com - 14 Sep 2005 11:57 GMT

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

Reply to this Message

Bob Phillips - 14 Sep 2005 13:03 GMT

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

Signature

HTH

Bob Phillips

> Hello Bob,
>
[quoted text clipped - 4 lines]
> Regards
> DR

Reply to this Message

Bruno Campanini - 14 Sep 2005 12:02 GMT

>A few days ago, I did receive very good help from Max and Dave to
> achieve the following formula,
[quoted text clipped - 15 lines]
> 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

Reply to this Message

Don Guillett - 14 Sep 2005 13:24 GMT

You could probably use FIND and OFFSET to do this.

Signature

Don Guillett
SalesAid Software
donaldb@281.com

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

> Basically I was looking to retrieve data based on multiple criteria and
> the formula is working just fine.
[quoted text clipped - 10 lines]
> Regards
> Daniel

Reply to this Message