WorksheetFunction.Match function problem [Excel 2010]

sektor · February 27th, 2010, 02:13 AM

I have the following function. I pass it value and array to search this value in array. If I pass "naked" digit, then all goes OK, but if I add some letter to this digit, then this error occurs: "Unable to get Match property of the WorksheetFunction class".

Here's code:

Code:

Sub GetData2Tariff()
.....
    Dim ls As Variant
    Dim lRow As Double
    Dim v2Tariff As Variant
    Dim vLsLESK As Variant
.....
    v2Tariff = Range("_2Tariff")    
    vLsLESK = Range("LsLESK")
.....
    ls = v2Tariff(f, 6)
    lRow = GetRow(ls, vLsLESK)
.....
End Sub

Private Function GetRow(ByVal vSeekValue As Variant, ByRef arr As Variant) As Double
    GetRow = Application.WorksheetFunction.Match(vSeekValue, arr, 0)
End Function

allenm · March 10th, 2010, 06:22 PM

Using Match with a match type of 0 will return a cell error if the value isn't found. In VBA this results in an error that will halt your code. If you want to use a spreadsheet function to do the find then you will have to trap the error:

Code:

Private Function GetRow(ByVal vSeekValue As Variant, ByRef arr As Variant) As Double

    GetRow = 0 'Just to show value returned when error occurs
    On Error GoTo GetRow_Error
        GetRow = Application.WorksheetFunction.Match(vSeekValue, arr, 0)
    On Error GoTo 0
GetRow_Error:

End Function

If you are just trying to find the row where a value in a range is found then I would suggest this would be better used for coding:

Code:

Private Function GetRowFound(vToFind As Variant, rFindIn As Range) As Long 'Row will never be a decimal number

'Finds first cell where match is found and returns the row.
'vToFind is what you are looking for.  Used variant so can be whatever is passed in
'rFindIn is a range of cells in a row, such as range("A:A") or range("A1:A250").
'rFound will be a reference to the cell where value is first found
  Dim rFound As Range
  Set rFound = rFindIn.Find(vToFind, LookIn:=xlValues)
  If rFound Is Nothing Then GetRowFound = 0 Else GetRowFound = rFound.Row

 End Function

It is up to you which way you do it but this way the code won't generate an error that requires exception handling.