Using Match with Worksheetfunction

[svjack9]

Hello,
I'm having a problem using worksheetfunction.match in my vba code. The problem lines look like this:

set ws = activeworkbook.worksheets("Storage")
ws.activate
intColumnNumber=application.worksheetfunction.match("Crop","A1:Z1",0)

I'm getting error 1004: "Unable to get the match property of the worksheetfunction class."

I've tried dimming intColumnNumber as type double without success. Any ideas?

Thanks in advance,
svjack9

 

[mikerickson]

Try

intColumnNumber=application.worksheetfunction.match("Crop",Range("A1:Z1"),0)

or better

intColumnNumber=application.match("Crop",Range("A1:Z1"),0)

Match needs a range, not a string, hence the Range(...) in both.

Excel has a quirk, if WorksheetFunction.Match doesn't find a match, it will cause a VB runtime error.
If Application.Match doesn't find a match, it will return the error value CVErr(xlErrNA), which makes for easier error checking.

Dim matchResult as Variant

matchResult = Application.Match("cat", Range("A:A"), 0)

If IsError(matchResult) Then
    MsgBox "cat is not in column A"
Else
    MsgBox "cat is in row " & matchResult & " of column A"
End If