Need help regarding ComboBox

[Vikram Dhemare]

Dear All,

I have userform. As soon as the user selects the value from combobox the
another test box lookup the value from certain table & returns the result in
textbox. (Here I used the vlookup function).
The combobox list is having alphanumerical value as well as numerical value.
If user selects the alphanumerical value from combobox, it working fine. But
if user selects the numerical value, it doesn't work.The Vlookup function
fails here.
Any idea?

Codes are here:

Private Sub cboPart_Change()
Dim LookupRange As Range
On Error Resume Next
Err.Clear
Set LookupRange = Worksheets("LookupLists").Range("A:I")

If cboPart <> "" Then
Me.LblDesc = Application.WorksheetFunction.VLookup _
(Me.cboPart.Value, LookupRange, 3, 0)

If Err.Number <> 0 Then
MsgBox "An Error occurred ! Plz. check the Part Code!"
Exit Sub
End If
End If
End Sub

 

[Dave Peterson]

I think you have at least a couple of options.

You could check to see if the part number is numeric:
if isnumeric(Me.cboPart.Value) then
'do the lookup using cdbl(Me.cboPart.Value)

or you could do it twice (untested):

Option Explicit
Private Sub cboPart_Change()
Dim LookupRange As Range
Dim res As Variant

Set LookupRange = Worksheets("LookupLists").Range("A:I")

If cboPart <> "" Then
res = Application.VLookup(Me.cboPart.Value, LookupRange, 3, 0)
If IsError(res) Then
'look as a number:
res _
= Application.VLookup(CDbl(Me.cboPart.Value), LookupRange, 3, 0)
End If

If IsError(res) Then
MsgBox "An Error occurred ! Plz. check the Part Code!"
Else
Me.LblDesc = res
End If
End If
End Sub

But I think I would use a third option.

I'd change my table to be text in column A, then just look for text.

 

[Vikram Dhemare]

Thanks a lot Mr. Dave. Its working (Second option)
Since last 3 weeks I was looking for the solution but couldn't get.
Thanks again.

--
Thanks,
Vikram P. Dhemare

I think you have at least a couple of options.

You could check to see if the part number is numeric:
if isnumeric(Me.cboPart.Value) then
'do the lookup using cdbl(Me.cboPart.Value)

or you could do it twice (untested):

Option Explicit
Private Sub cboPart_Change()
Dim LookupRange As Range
Dim res As Variant

Set LookupRange = Worksheets("LookupLists").Range("A:I")

If cboPart <> "" Then
res = Application.VLookup(Me.cboPart.Value, LookupRange, 3, 0)
If IsError(res) Then
'look as a number:
res _
= Application.VLookup(CDbl(Me.cboPart.Value), LookupRange, 3, 0)
End If

If IsError(res) Then
MsgBox "An Error occurred ! Plz. check the Part Code!"
Else
Me.LblDesc = res
End If
End If
End Sub

But I think I would use a third option.

I'd change my table to be text in column A, then just look for text.