VBA Match Function "Method 'Match' of Object 'WorksheetFunction' Failed"

Question

I'm trying to use the Match VBA function in a programming project in Excel to get the first row number with a "1" in it. The code I'm using is:

Dim BorradorVar As Integer
BorradorVar = WorksheetFunction.Match(1, Sheets("Usuarios").Range("A1,A100"), 0)

Can someone explain what I'm doing wrong? Thanks!

Thank you! It works perfectly now :D – mataxu Jun 10 '15 at 14:36 — mataxu, Jun 10 '15 at 14:36

score 1 · Accepted Answer · edited May 23 '17 at 12:15

You should refer to Range as

Range("A1:A100")

instead of

Range("A1,A100")

Using comma you refer to A1 and A100 only.

Also, if you are not sure if there is a match or not, you can use Application.Match and store the result in a Variant variable instead. The difference is that Application.Match returns an error when it fails, and then you can check if it has failed or not.

Example (check MSDN for full code):

Dim var As Variant

var = Application.Match(Cells(iRow, 1).Value, Worksheets(iSheet).Columns(1), 0)

If Not IsError(var) Then
    Debug.Print "There is actually a match"
Else
    Debug.Print "No match found"
End IF

A word of warning: If you match after row 32,767 your code will not run, because it exceeds the range of Integer. I suggest using Long instead of Integer. Using Long is better practice in VBA anyway.

Finally, note that if someone changes the name of the worksheet, this code will not run. It is safer to refer to the worksheet by using its sheet ID instead of its name (Disclaimer: I have written the accepted response to that question).

VBA Match Function "Method 'Match' of Object 'WorksheetFunction' Failed"

1 Answers1