PDA

View Full Version : [SOLVED] Application.match failing



lifeson
06-27-2008, 11:08 PM
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.:banghead:

Ken Puls
06-27-2008, 11:28 PM
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
06-28-2008, 12:49 AM
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
06-28-2008, 01:40 AM
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
06-28-2008, 03:11 AM
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