Extracting AlphaNumeric left of specified value in a ListBox

[dversloot1]

Hello,

I've created a user form that has a list box populated with a fairly standardized format - a product ID or model number followed by a dash ( - ) then followed by a description. Based on the users selection of an item in the listbox, the model number or ID will be pasted into a range.

An error occurs when a Model Number list box item is selected but not when a product ID is selected.

The difference between the two are AlphaNumeric vs only numeric.

USBLT15CMB - Product Description 1
1121 - Product Description 2

the code is:
Private Sub CommandButton1_Click()
Dim ID As Long
Dim Row As Long
Dim Cnt As Long

Cnt = InStr(1, ListBox2.Value, "-")
ID = Left(ListBox2.Value, Cnt - 1)
On Error Resume Next
Row = Application.WorksheetFunction.Match(ID, Sheets("Data").Range("B1:B2000"), 0)
.....
Copy some stuff, paste some stuff
......
End Sub

In simpler terms, how do I extract the "USBLT12CMB" out of the ListBox selection and paste it in a cell?

Extracting the "1121" from the other product works with the code but "USBLT15CMB" does not.

Any suggestions how to work around this?

 

[Domenic]

Since you're assigning both a string and a number to your variable, you should declare it as a Variant. Therefore, try replacing...

Dim ID As Long

with

Dim ID As Variant

Also, you might want to get rid of the extra space after you've extracted your ID or model number...

ID = Trim(Left(ListBox2.Value, Cnt - 1))