CODEWITHSUNDEEP
excelvba
Royce
Royce

Reputation: 1595

Error while using INDEX+MATCH in VBA (based on Excel formula)?

I'm trying to get content of mapping table based on 2 criteria.

Find below the formula I would like to convert

=INDEX(MappingTables!Y2:Y19;MATCH(1;(MappingTables!V2:V100=AE2)*(MappingTables!W2:W100=Y2);0))

Find below the current VBA code I'm using (where RANGE are constants) :

res = Application.Index(mappingTables.Range(RANGE1), Application.Match(1, (mappingTables.Range(RANGE2) = criteria1) * (mappingTables.Range(RANGE3) = criteria2), 0))

However, I'm getting below error

Unidentified error detected. Process terminated.

Can you please help me to understand where is the issue ?

Thank you,

Regards,

Upvotes: 1

Views: 38

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

A construct like (mappingTables.Range(RANGE2) = criteria1) * (mappingTables.Range(RANGE3) = criteria2) does not work in VBA, you can only do this with an array of data in formulas.

This mappingTables.Range(RANGE3) is an array of values and VBA cannot compare entire arrays without looping, nor can it multiply entire arrays without looping.

In VBA you would have to loop through that data to calulate the result for each data point in the array.

To avoid that I recommend to use Evaluate and a formula:

res = Evaluate("=INDEX(MappingTables!Y2:Y19,MATCH(1,(MappingTables!V2:V100=AE2)*(MappingTables!W2:W100=Y2),0))")

or better

res = Evaluate("=INDEX(MappingTables!" & RANGE1 & ",MATCH(1,(MappingTables!" & RANGE2 & "=" & Criteria1 & ")*(MappingTables!" & RANGE2 & "=" & Criteria2 & "),0))")

Upvotes: 1

Related Questions