Error using the MATCH worksheet function

[rdionne]

I get an error when I try to use MATCH from the immediate window:

?Application.WorksheetFunction.Match(0.2,A1:A10,1) A1:A10 contains a sequential range of numbers from -.2 to 0.5 . I get a compile error "Expected list separator or )". From Excel help, this seems to be the correct syntax. What am I missing?

 

[rdionne]

It seems that the help examples only work on a spreadsheet, not from VBA. VBA requires a range.This works:

?Application.WorksheetFunction.Match(0.2,Worksheets("Sheet1").range("A1:A10"),1)
5

 

[Guest]

Change that to

Application.Match(0.2,Range("A1:A10"),1)

It's a little more reliable than using the WorksheetFunction

 

[OdinsDream]

You can also fix the problem by changing your quotations to brackets:

Application.Match(0.2, [A1:B10], 0)