CODEWITHSUNDEEP
excelvba
user3035828
user3035828

Reputation: 73

Application.WorksheetFunction.Match method

I have seen a lot of Topics to the "unable to get the match property of the Worksheetfunction class" problem. But I can't get my code fixed.

Why isn't this code work?

rowNum = Application.WorksheetFunction.Match(aNumber, Sheet5.Range("B16:B615"), 0)

But a few rows higher this code works:

rowNum2 = Application.WorksheetFunction.Match(originCode, Sheet7.Range("B10:B17"), 0)

The only difference between my two lines is that in rowNum2 I used a String for look up and in rowNum a integer. Is it possible that the look up Value needs to be a String?

@Update on my Problem

Select Case service
Case "Low Cost"
MsgBox Sheet5.Cells(16, "B") 'Gets value 0.5
Set Rng = Sheet5.Range("B16:B615")

   If Not IsError(Application.Match("0.5", Rng, 0)) Then 'But jumps to Else
    rowNum = Application.Match(Weight, Rng, 0) 'Weight = 0.5
    MsgBox rowNum
Else
    MsgBox "error"
End If

Case "Standard"

Case "Express"

Case Else

End Select

@UPDATE 2

!!! Take care that "0.5" is a String and not 0.5 So 0.5 is not "0.5" (that was my error in the code)

Upvotes: 7

Views: 143719

Answers (1)

Sam
Sam

Reputation: 7303

You are getting this error because the value cannot be found in the range. String or integer doesn't matter. Best thing to do in my experience is to do a check first to see if the value exists.

I used CountIf below, but there is lots of different ways to check existence of a value in a range.

Public Sub test()

Dim rng As Range
Dim aNumber As Long

aNumber = 666

Set rng = Sheet5.Range("B16:B615")

    If Application.WorksheetFunction.CountIf(rng, aNumber) > 0 Then

        rowNum = Application.WorksheetFunction.Match(aNumber, rng, 0)

    Else
        MsgBox aNumber & " does not exist in range " & rng.Address
    End If

End Sub

ALTERNATIVE WAY

Public Sub test()
    Dim rng As Range
    Dim aNumber As Variant
    Dim rowNum As Long

    aNumber = "2gg"

    Set rng = Sheet5.Range("B1:B20")

    If Not IsError(Application.Match(aNumber, rng, 0)) Then
        rowNum = Application.Match(aNumber, rng, 0)
        MsgBox rowNum
    Else
        MsgBox "error"
    End If
End Sub

OR

Public Sub test()
    Dim rng As Range
    Dim aNumber As Variant
    Dim rowNum As Variant

    aNumber = "2gg"

    Set rng = Sheet5.Range("B1:B20")

    rowNum = Application.Match(aNumber, rng, 0)

    If Not IsError(rowNum) Then
        MsgBox rowNum
    Else
        MsgBox "error"
    End If
End Sub

Upvotes: 16

Related Questions