Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > ComboBox with multiple criteria
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default ComboBox with multiple criteria
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default ComboBox with multiple criteria
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" wrote in message
...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default ComboBox with multiple criteria
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" wrote in message
...
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default ComboBox with multiple criteria
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" wrote in message
...
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default ComboBox with multiple criteria
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" wrote in message
...
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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default ComboBox with multiple criteria
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" wrote in message
...
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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default ComboBox with multiple criteria
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" wrote in message
...
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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default ComboBox with multiple criteria
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" wrote in message
...
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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
match multiple criteria ina range from multiple criteria multiplet RG Excel Worksheet Functions 8 September 28th 07 04:21 AM
Combobox list - only with rows that fit the criteria Vikram Excel Discussion (Misc queries) 0 July 28th 06 12:43 AM
Excel Combobox with additional selection criteria sven_dau[_2_] Excel Programming 5 July 19th 06 01:26 PM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Saleem Excel Worksheet Functions 1 January 12th 05 10:54 AM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Arain Excel Worksheet Functions 1 January 12th 05 08:33 AM


All times are GMT +1. The time now is 03:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement