Extracting AlphaNumeric left of specified value in a ListBox

dversloot1

Board Regular
Joined
Apr 3, 2013
Messages
113
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
Since you're assigning both a string and a number to your variable, you should declare it as a Variant. Therefore, try replacing...

Code:
Dim ID As Long
with

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

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

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top