ComboBox with multiple criteria

[Vikram Dhemare]

Hi,
I have created a userform wherein there is a combobox which refers the
masterlist
contents columns like:
Col. A = Old Item Code Col. B = New Item Code & Col. C is Description.
Combobox lookups the list for Col. A, i.e. Old Item Code. Upto here it is
working fine.
The problem is, some time user doesn't know the Old item code, he only knows
the new item code.
Now in such cases, the user will enter the new item code in combobox & then
the combobox should lookup the value from Col. B instead of Col. A
The combobox should refer either Col. A List if does not found then look the
value in Col. B and return the result.
Hope I explained it correctly.
Is it possible?

Looking forward!
=================================================
Here is some code:

For Each cPart In ws.Range("PartIDList")
With Me.cboPart
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
=====================================================
As soon as the user enter the value in combo box, it returns

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:
On Error Resume Next
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
=====================================================
Master Sheet Looks Like:

OldImCode NewImCode ImDesc.
ATM0028 TET018A WOOD STOCK FR LH OLD
ATM0030 TET020A WOOD STOCK FR RH OLD
ATM0031 TET021A WOOD STOCK RR LH OLD
ATM0032 TET022A WOOD STOCK RR RH OLD
AND SO ON.......

 

[Tom Ogilvy]

Private Sub cboPart_Change()
Dim LookupRange As Range
Dim LookupRangeN As Range
Dim res As Variant
Set LookupRange = Worksheets("LookupLists").Range("A:I")
Set LookupRangeN = LookupRange.Offset(0,1).Range("A:G")
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
res = Application.VLookup(Me.cboPart.Value, LookupRangeN, 2, 0)
End if
If IsError(res) then
'look as a number:
res _
= Application.VLookup(CDbl(Me.cboPart.Value), LookupRangeN, 2, 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

 

[Vikram Dhemare]

Thanks for early response Mr. Tom. I have tried the code supplied by you, but
it didn't work. Getting an error message "Type Mismatch".

Any help?
--
Thanks,
Vikram P. Dhemare

Private Sub cboPart_Change()
Dim LookupRange As Range
Dim LookupRangeN As Range
Dim res As Variant
Set LookupRange = Worksheets("LookupLists").Range("A:I")
Set LookupRangeN = LookupRange.Offset(0,1).Range("A:G")
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
res = Application.VLookup(Me.cboPart.Value, LookupRangeN, 2, 0)
End if
If IsError(res) then
'look as a number:
res _
= Application.VLookup(CDbl(Me.cboPart.Value), LookupRangeN, 2, 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

 

[Tom Ogilvy]

Which line of code?

Since it is exactly as you had, it would be difficult to imagine where there
would be a problem.

--
Regards,
Tom Ogilvy

Thanks for early response Mr. Tom. I have tried the code supplied by you, but
it didn't work. Getting an error message "Type Mismatch".

Any help?

 

[Vikram Dhemare]

Receiving an error message on the fifth line of code i.e.

If IsError(res) Then
'look as a number:

Error Here >>>> res _
= Application.VLookup(CDbl(Me.cboPart.Value),
LookupRange, 3, 0)
End If

Thanks,
Vikram P. Dhemare

 

[Dave Peterson]

Tom deleted your "on error resume next" line. That line made it so your
original code would ignore the non-numeric looking entries.

You could add it back or just check for a numeric string first:

Option Explicit
Private Sub cboPart_Change()
Dim LookupRange As Range
Dim LookupRangeN As Range
Dim res As Variant
Set LookupRange = Worksheets("LookupLists").Range("A:I")
Set LookupRangeN = LookupRange.Offset(0, 1).Range("A:G")
If cbopart <> "" Then
res = Application.VLookup(Me.cbopart.Value, LookupRange, 3, 0)
If IsError(res) Then
If IsNumeric(Me.cbopart.Value) Then
res = Application.VLookup(CDbl(Me.cbopart.Value), _
LookupRange, 3, 0)
End If
End If
If IsError(res) Then
res = Application.VLookup(Me.cbopart.Value, LookupRangeN, 2, 0)
End If
If IsError(res) Then
If IsNumeric(Me.cbopart.Value) Then
res = Application.VLookup(CDbl(Me.cbopart.Value), _
LookupRangeN, 2, 0)
End If
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

 

[Vikram Dhemare]

Sorry!
Still the res returns error number 2042.

 

[Dave Peterson]

That's ok. That means that there was no match.

But the iserror(res) will catch that error and follow that branch.