Error using the MATCH worksheet function

rdionne · Feb 27th, 2002, 10:55 AM

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 · Feb 27th, 2002, 11:21 AM

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

Feb 27th, 2002, 12:10 PM

Change that to

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

It's a little more reliable than using the WorksheetFunction

OdinsDream · Jun 17th, 2002, 09:03 AM

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

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 27th, 2002, 10:55 AM	#1
rdionne New Member Join Date: Feb 2002 Location: Texas Instruments Posts: 47	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?

Feb 27th, 2002, 11:21 AM	#2
rdionne New Member Join Date: Feb 2002 Location: Texas Instruments Posts: 47	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

Feb 27th, 2002, 12:10 PM	#3
Guest Posts: n/a	Change that to Application.Match(0.2,Range("A1:A10"),1) It's a little more reliable than using the WorksheetFunction

Jun 17th, 2002, 09:03 AM	#4
OdinsDream Board Regular Join Date: May 2002 Posts: 541	You can also fix the problem by changing your quotations to brackets: Application.Match(0.2, [A1:B10], 0)