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)
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
Source: Stackoverflow.com