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.......

--
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

--
Regards,
Tom Ogilvy


"Vikram Dhemare" <(E-Mail Removed)> wrote in message
news:B2C23CE4-CB8C-4722-B91B-(E-Mail Removed)...
> 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.......
>
> --
> Thanks,
> Vikram P. 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


"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Vikram Dhemare" <(E-Mail Removed)> wrote in message
> news:B2C23CE4-CB8C-4722-B91B-(E-Mail Removed)...
> > 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.......
> >
> > --
> > Thanks,
> > Vikram P. Dhemare
>
>
>

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


"Vikram Dhemare" wrote:

> 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
>
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Vikram Dhemare" <(E-Mail Removed)> wrote in message
> > news:B2C23CE4-CB8C-4722-B91B-(E-Mail Removed)...
> > > 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.......
> > >
> > > --
> > > Thanks,
> > > Vikram P. 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


"Tom Ogilvy" wrote:

> 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
>
>
> "Vikram Dhemare" wrote:
>
> > 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
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Vikram Dhemare" <(E-Mail Removed)> wrote in message
> > > news:B2C23CE4-CB8C-4722-B91B-(E-Mail Removed)...
> > > > 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.......
> > > >
> > > > --
> > > > Thanks,
> > > > Vikram P. Dhemare
> > >
> > >
> > >

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 wrote:
>
> 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
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> >
> > "Vikram Dhemare" wrote:
> >
> > > 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
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Vikram Dhemare" <(E-Mail Removed)> wrote in message
> > > > news:B2C23CE4-CB8C-4722-B91B-(E-Mail Removed)...
> > > > > 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.......
> > > > >
> > > > > --
> > > > > Thanks,
> > > > > Vikram P. Dhemare
> > > >
> > > >
> > > >

--

Dave Peterson

Sorry!
Still the res returns error number 2042.


--
Thanks,
Vikram P. Dhemare


"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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
> > >
> > >
> > > "Vikram Dhemare" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "Vikram Dhemare" <(E-Mail Removed)> wrote in message
> > > > > news:B2C23CE4-CB8C-4722-B91B-(E-Mail Removed)...
> > > > > > 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.......
> > > > > >
> > > > > > --
> > > > > > Thanks,
> > > > > > Vikram P. Dhemare
> > > > >
> > > > >
> > > > >
>
> --
>
> Dave Peterson
>

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

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



Vikram Dhemare wrote:
>
> Sorry!
> Still the res returns error number 2042.
>
> --
> Thanks,
> Vikram P. Dhemare
>
> "Dave Peterson" wrote:
>
> > 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 wrote:
> > >
> > > 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
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Vikram Dhemare" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > > "Vikram Dhemare" <(E-Mail Removed)> wrote in message
> > > > > > news:B2C23CE4-CB8C-4722-B91B-(E-Mail Removed)...
> > > > > > > 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.......
> > > > > > >
> > > > > > > --
> > > > > > > Thanks,
> > > > > > > Vikram P. Dhemare
> > > > > >
> > > > > >
> > > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson