left vlookup in vba

[carstenp]

I found this formula which works in excel, but when I try to put it in vba i get an error message that it is 'unable to get the Match property of the WorksheetFunction class'. Here's my code

 offsetmsg = Application.WorksheetFunction.Index(offsetwelllookup, Application.WorksheetFunction.Match(Distmsg, leastdist, 0))

Any ideas?

 

[mvptomlinson]

Change "Application.WorksheetFunction.Match" to just "Application.Match"

 

[AlphaFrog]

The syntax of your line of code seems OK. It just can't find a match.

Can't diagnose why with just that info.

• Maybe there is no match.
• Maybe there is a problem with the range variables Distmsg or leastdist

 

[AlphaFrog]

One other thought, if Distmsg and leastdist are named ranges, you would use something like this...

offsetmsg = Application.Index(Range("offsetwelllookup"), Application.Match(Range("Distmsg"), Range("leastdist"), 0))