Run time error '1004' Unable to get the Ma

Question

In my macro, I have the following code :

i = Application.WorksheetFunction.Match(str_accrual, Range(Selection, Selection.End(xlToRight)), 0)

where 'str_accrual' is a string captured earlier to this line and the Range selected is in a single row say from "A1" to "BH1" and the result will be a number which is the position of that string in that range selected.

When I run the macro, I get the error:

Run time error '1004' Unable to get the Match propertyof the WorksheetFunction class

But when I run the macro line by line using (F8) key, I don't get this error but when I run the macro continuously I get the error. Again, if the abort the macro and run it again the error doesn't appear.

Answer 1

tons of posts on this error but no solution as far as I read the posts. It seems that for various worksheet functions to work, the worksheet must be active/visible. (That's at least my latest finding after my Match() was working randomly for spurious reasons.)

I hoped the mystery was solved, though activating worksheets for this kind of lookup action was a pain and costs a few CPU cycles.

So I played around with syntax variations and it turned out that the code started to work after I removed the underscore line breaks, regardless of the worksheet being displayed. <- well, for some reason I still had to activate the worksheet :-(

'does not work

'Set oCllHeader = ActiveWorkbook.Worksheets("Auswertung").Cells(oCllSpielID.Row, _
                    Application.Match( _
                        strValue, _
                        ActiveWorkbook.Worksheets("Auswertung").Range( _
                            oCllSpielID, _
                            ActiveWorkbook.Worksheets("Auswertung").Cells(oCllSpielID.Row, lastUsedCellInRow(oCllSpielID).Column)), _
                            0))

'does work (removed the line breaks with underscore for readibility) <- this syntax stopped working later, no way around activating the worksheet :-(

Set oCllHeader = ActiveWorkbook.Worksheets("Auswertung").Cells(oCllSpielID.Row, Application.Match(strValue, ActiveWorkbook.Worksheets("Auswertung").Range(oCllSpielID, ActiveWorkbook.Worksheets("Auswertung").Cells(oCllSpielID.Row, lastUsedCellInRow(oCllSpielID).Column)), 0))

In the end I am fretting running into more realizations of this mystery and spending lots of time again.

cheers

Answer 2

I used "If Not IsError" and the error kept showing. To prevent the error, add the following line as well:

On Local Error Resume Next

Answer 3

I was getting this error intermittently. Turns out, it happened when I had a different worksheet active.

As the docs for Range say,

When it's used without an object qualifier (an object to the left of the period), the Range property returns a range on the active sheet.

So, to fix the error you add a qualifier:

Sheet1.Range

Answer 4

That is what you get if MATCH fails to find the value.

Answer 5

I tried several times. It seems that if there is no match, the expression will prompt this error

if you want to catch the error, use Application.Match instead

Then you can wrap it with isError

Answer 6

Try this instead:

If Not IsError(Application.Match(str_accrual, Range(Selection, Selection.End(xlToRight)), 0)) Then
    i = Application.Match(str_accrual, Range(Selection, Selection.End(xlToRight)), 0)
Else
    'do something if no match is found

End If

Update

Here is better code that does not rely on Selection except as a means of user-input for defining the range to be searched.

Sub Test()

Dim str_accrual As String
Dim rngToSearch As Range

str_accrual = InputBox("Search for?")

Set rngToSearch = Range(Selection, Selection.End(xlToRight))

If Not IsError(Application.Match(str_accrual, rngToSearch, 0)) Then
    i = Application.Match(str_accrual, rngToSearch, 0)
    MsgBox i
Else
    MsgBox "no match is found in range(" & rngToSearch.Address & ")."
End If
End Sub