MATCH Problem in VBA

[ennui101]

Application.Match(ItemNumber, Worksheets("main").Range("d11.d200"), 0))

I have a column of data which has mixed "pure" numbers and numbers with alpha characters attached; something like 1,2a,2b,3,4,5,6,7a,7b,etc.
My search element (ItemNumber) is always string, despite its name. I get "type mismatch" errors trying to do the match. I have tried every trick I know plus a few from web searches to get Excel to convert that column to text. I can convince Excel (that is, the MATCH function works in Excel), but not VBA. So far the only technique which works in VBA is to add an alpha prefix to each element of the column, e.g. i1,i2a,i2b,i3,i4,etc.

That is hardly how I'd like to solve my problem. ANY ideas most welcome.

 

[ennui101]

With more experimentation, I find I need to restate the problem. If I manually manipulate the data in Excel, I can create a column which is treated as text by Excel and string by VBA. However, I cannot manipulate the data in VBA to achieve the same result.

So the problem is more of a conversion to string than a match problem.

 

[PJ_in_FL]

Define the variable getting the result as Variant, then check for Iserror(result) in case no match is found.

 

[eric cannelora]

Hi, you can also trap the error and use CLng(ItemNumber) when the error = 1004:

On Error Resume Next
Application.WorksheetFunction.Match(ItemNumber, Worksheets("main").Range("d11.d200"),0) 
If Err.Number = 1004 Then
    Application.WorksheetFunction.Match(CLng(ItemNumber), Worksheets("main").Range("d11.d200"),0)
End If

 

[ennui101]

That's an approach I hadn't considered and better than what I'm doing now. But I'm so annoyed I can't establish that column as string that I'd like to get to the bottom of it for my own education. There has to be a way!

But thank you for your creative suggestion. It's going into the code right now as a much better workaround.

Peter

 

[Marcelo Branco]

Hi Peter,

Maybe this (assuming Range("D11:D20") contains a 3)

Sub test()
    Dim ItemNumber As String
    Dim result As Long
    
    Range("D11:D20").TextToColumns Destination:=Range("D11"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
    
    ItemNumber = 3
    result = Application.Match(ItemNumber, Range("D11:D20"), 0)
    
    MsgBox result
    
End Sub

HTH

M.

 

[Marcelo Branco]

Peter,

I used for test-purposes D11:D20. I have seen in the previous posts that your real range is D11:D200 in sheet main.

Adjust it to Sheets("main").Range("D11:D200")

M.

 

[ennui101]

Marcelo, thanks for the try. Not sure why it doesn't work, but it doesn't.
Still get type mismatch error, and a check of the numbers on the Excel sheet shows that they are still isnumber(Dx) = true.

Peter

 

[ennui101]

OK, here's the status:

with "on error resume next" in the code, the match returns error 2042 (no match found) in the result and err.number = 0. Not very helpful.

So here's what I have for code at the moment which seems to work:

On Error Resume Next
Dim ItemNumber As String
Dim ItemDescr As String
Dim result

' go find the item and move description to text box

ItemNumber = CStr(Me.fItem)

result = Application.Match(ItemNumber, Worksheets("main").Range("d11.d200"), 0)

If Not IsNumeric(result) Then
result = Application.Match(CLng(ItemNumber), Worksheets("main").Range("d11.d200"), 0)
End If

If IsNumeric(result) Then
ItemIndex = 10 + result
ItemDescr = Application.Index(Worksheets("main").Range("e1:e200"), ItemIndex)
Else
ItemDescr = "NO SUCH ITEM in data base" 'is there an item description?
End If

 

[ennui101]

Peter,

I used for test-purposes D11:D20. I have seen in the previous posts that your real range is D11:D200 in sheet main.

Adjust it to Sheets("main").Range("D11:D200")

M.

Marcello, thanks for being persistent. I read the 20 as 200 and was using 25 as my test case. This approach does seem to work. I just have to filter for cases where there is no match.
I'll try this in the live code and see if I can make it bullet-proof.

Peter