Find Row Using 3 ComboBoxes As Criteria

[Minitman]

Greetings,

I have three nested ComboBoxes which are working (Thanks to Norman
Jones).

The second half of my problem is to use these three ComboBoxes as
criteria to get the data from the next two columns and place it into
two TextBoxes(TB5 & TB6) on my UserForm.

The code I have been fighting with is giving me a type mismatch error!

Here is that code:

For Each c In Range("ReceiptList")
If CB1.Value = Range("ReceiptList").Offset(0, 1).Value And _
CB2.Value = Range("ReceiptList").Offset(0, 2).Value _
And CB3.Value = Range("ReceiptList").Offset(0, 3) _
Value Then
TB5.Value = Range("ReceiptList").Offset(0, 4).Value
TB6.Value = Range("ReceiptList").Offset(0, 5).Value
Exit Sub
End If
Next c

CB1, CB2 & CB3 are ComboBoxes and TB5 & TB6 are TextBoxes all 5 are on
a UserForm. Range "ReceiptList" is on a Sheet called "Data". It
contains all 5 columns. CB1 gets it's Rowsource from column A, CB2
gets it's RowSource from column B and CB3 gets it's RowSource from
column C, TB5 is looking for the matching row in column D and TB6 is
looking for in column E.

I have a sample workbook that I have been working with if anyone would
like to see it.

Any help or suggestions are welcome.

-Minitman

 

[Guest]

For Each c In Range("ReceiptList").columns(1).Cells
If CB1.Value = c.Value And _
CB2.Value = c.Offset(0, 1).Value _
And CB3.Value = c.Offset(0, 2) _
Value Then
TB5.Value = c.Offset(0, 3).Value
TB6.Value = c.Offset(0, 5).Value
Exit Sub
End If
Next c

 

[Minitman]

Hey Tom,

Thanks for the reply, that takes care of the mismatch error.

Now I see that this approach is not working.

What is the best way to get the value of columns D & E in the row that
matches CB1, CB2 & CB3 on columns A, B & C.

Any ideas? I am at a loss, everything that I tried has failed.

looking forward to hearing from you soon.

-Minitman

 

[Guest]

That code should work. If it isn't you need to debug it and see why it isn't
working.

One problem could be that some of your columns where you need to match are
numbers. Values in a combobox are always strings (even if they look like
numbers). this could be your problem. In the example I set up and tested,
the second column was a number, so I converted the cb2.value like this
clng(cb2.value) = cell.offset(0,1).value

you could also do

cb2.value = cell.offset(0,2).Text

as long as you don't have special formatting applied that doesn't appear in
the combobox.

this was taken from a userform where it was working very well:

Private Sub CommandButton1_Click()
For Each c In Range("ReceiptList").Columns(1).Cells
If CB1.Value = c.Value _
And CLng(CB2.Value) = c.Offset(0, 1).Value _
And CB3.Value = c.Offset(0, 2).Value Then
TB5.Value = c.Offset(0, 3).Value
TB6.Value = c.Offset(0, 4).Value
Exit Sub
End If
Next c

End Sub

 

[Minitman]

Hey Tom,

I tried that , unfortunately it didn't work

Column B is formatted as date/time (mm/dd/yyyy h:mm AM/PM).
CLng(CB2) errors out with that type mismatch error.

Is there anything else I can try?

 

[Guest]

Yes, you need to develop a valid test. I wasn't suggesting clng for dates.
If you want to test a date I would suggest

format(c.offset(0,1).Value2, "mm/dd/yyyy hh:mm AM/PM") = _
format(cdate(cb2.Value)),"mm/dd/yyyy hh:mm AM/PM")

I realize it might be frustrating, but you need to instrument your code a
bit and see where you might be having problems - otherwise you might need to
create watch values or step through your code.

--
Regards,
Tom Ogilvy

 

[Minitman]

Hey Tom,

That test works great. But it was still not working so I did some
more testing and rearranged the if/then's to be nesting and put a
msgbox between each layer. Everything worked until I got to the
innermost if/then (dNum is a list of numbers - the item number of that
particular Date/Time). Using that CLng() trick you showed me earlier,
I wrapped both sides of the formula (turns out I only needed to wrap
the c.Offset(0, 2) portion, CB3,Value must already be in that format)
and then it worked perfectly!

Here is the final code(without the MsgBoxes):

For Each c In Range("ReceiptList").Columns(1).Cells
If CB1.Value = c.Value Then
If Format(c.Offset(0, 1).Value, "mm/dd/yyyy hh:mm AM/PM") = _
Format(CDate(CB2.Value), "mm/dd/yyyy hh:mm AM/PM") Then
If CLng(CB3.Value) = CLng(c.Offset(0, 2)) Then
TB5.Value = c.Offset(0, 3).Value
TB6.Value = c.Offset(0, 4).Value
Exit Sub
End If
End If
End If
Next c

Thank you very much. Your help is greatly appreciated.

-Minitman