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]

Marcello, I have in fact incorporated your approach, and with a simple test for numeric result, I can differentiate the "no-match" situation. Again thank you and thanks also to Eric for his workable solution.

Private Sub fItem_Exit(ByVal Cancel As MSForms.ReturnBoolean)
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)
Range("D11:D200").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

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

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
Me.fMsgBox = ItemDescr

End Sub

 

[Marcelo Branco]

I'm happy that it's working and tks for the feedback

M.