Using INDEX and MATCH in vba.

[Folshot]

Hi Folks,

I am having issues implementing the use of INDEX and MATCH in a macro, basically I would need a macro that loop through all cells of a column applying a INDEX and MATCH formula to it only if a value is found (i.e. if the formula does not return #N/A), otherwise do nothing. So far I wrote the following:

Please Login or Register  to view this content.

However I am having issues with the above line in bold (I tried to write a statement to apply the INDEX & MATCH formula if its result is not #N/A), the macro stops at that line with Run-time errror 1004.

The formula in itself is:

Please Login or Register  to view this content.

Could anyone point me to the right direction in order to convert such a formula into vba?
I googled most of the evening and could not find the right syntax.

Thanks a lot,
Ant

[zbor]

Did you try just Evaluate

Please Login or Register  to view this content.

[Izandol]

You may test match result with Iserror:

Please Login or Register  to view this content.

[Folshot]

Hi zbor, Izandol,

Thanks a lot to both of you, both codes work great. To summarise, I can either use:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The remaining question, what is the most efficient code? Is there any difference between both of them?

PS: Why cannot I set i to be an Integer? After all the row # is always going to be a number?

[Izandol]

Integer may only hold numbers up to 32767. Worksheet has many more rows than this.

[Olly]

Just to add, that naming a variable 'index', the same as a worksheet function name, is a recipe for confusion...

[Folshot]

Integer may only hold numbers up to 32767

Thanks Izandol, I was not aware of this.

aming a variable 'index', the same as a worksheet function name, is a recipe for confusion...

You are very right and just realised it is where I messed up in my original code, apologies for this.

I have now 3 different alternatives to use INDEX & MATCH in vba:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Are there any advantages of using one over the other?

[Izandol]

Last one is more efficient. Second one may be written to be more efficient:

Please Login or Register  to view this content.

[Folshot]

Thanks a lot Izandol, I will go with your suggestion.