Application.match failing

[lifeson]

I use this to find which row contains the data I am looking for

With wsTree
    r = .Cells(Rows.Count, "A").End(xlUp).Row
    .Select
    Select Case key
        Case 1
            f = Application.WorksheetFunction.Match(compID, wsTree.Columns(6), 0)
'           MsgBox "component " & compID & " is on row " & f
            .Cells(f, "H").Value = Price
        Case 2
            f = Application.WorksheetFunction.Match(packID, wsTree.Columns(6), 0)
            MsgBox "Pack " & packID & " is on row " & f
            .Cells(f, "H").Value = Price
    End Select
End With

This works fine for items such as ABC123 etc

 f= Application.WorksheetFunction.Match(compID, wsTree.Columns(6), 0)

But using the same routine but for a packID

 f = Application.WorksheetFunction.Match(packID, wsTree.Columns(6), 0)

which can be either an alphanumeric mix or numeric only it gives the error "unable to get the match property of the worksheet function"

packID is a string, I have tried it as a variant aswell and still get the same problem.

[Ken Puls]

Try converting your packID to a string by wrapping it in the CStr() function:

f = Application.WorksheetFunction.Match(CStr(packID), wsTree.Columns(6), 0)

Just be aware that you should probably convert all of your packID references to CStr(packID). It sound to me like some are being interpreted as values, and others strings. This will make it consistent. (Variants are interpreted at runtime, so it doesn't solve the issue.)

HTH,

[RichardSchollar]

Using Application.Match rather than Application.WorksheetFunction.Match will also prevent a runtime error being generated should Match fail to find a matching value. You can then test the value returned by Match with IsError to determine if a match was found:

f = Application.Match(compID, wsTree.Columns(6), 0) 
If IsError(f) Then
  MsgBox "No match found!"
  '...
Else
  'rest of code
  '...
End If

Richard

[Bob Phillips]

Using Application.Match rather than Application.WorksheetFunction.Match will also prevent a runtime error being generated should Match fail to find a matching value. You can then test the value returned by Match with IsError to determine if a match was found:

f = Application.Match(compID, wsTree.Columns(6), 0) 
If IsError(f) Then
  MsgBox "No match found!"
  '...
Else
  'rest of code
  '...
End If

Richard

f has to be a variant variable for this to work, and I don't know why but I just resist using a variant for an index variable. I tend to use

Dim f As Long
On Error Resume Next
f = Application.Match(compID, wsTree.Columns(6), 0)
On Error GoTo 0
If f > 0 Then
    'rest of code
    '...
Else
    MsgBox "No match found!"
    '...
End If

[lifeson]

After a bit of mixing of answers I found this seems to work reliably

Try the packID as as string if that fails try it as numeric

With wsTree
    r = .Cells(Rows.Count, "A").End(xlUp).Row
    .Select
    Select Case key
        Case 1
            f = Application.WorksheetFunction.Match(compID, wsTree.Columns(6), 0)
            'MsgBox "component " & compID & " is on row " & f
            .Cells(f, "H").Value = Price
        Case 2
            On Error Resume Next
            f = Application.Match(CStr(packID), wsTree.Columns(6), 0)
            If Err = 0 Then
                ' PackID is alphanumeric
                MsgBox "Pack " & packID & " is on row " & f
                .Cells(f, "H").Value = Price
            Else
                f = Application.Match(CLng(packID), wsTree.Columns(6), 0)
                ' pack ID Is numeric
                MsgBox "Pack " & packID & " is on row " & f
                .Cells(f, "H").Value = Price
            End If
            On Error GoTo 0