Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row Using 3 ComboBoxes As Criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row Using 3 ComboBoxes As Criteria
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 -- Regards, Tom Ogilvy "Minitman" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row Using 3 ComboBoxes As Criteria
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 On Wed, 19 Jul 2006 09:47:02 -0700, Tom Ogilvy wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row Using 3 ComboBoxes As Criteria
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 -- Regards, Tom Ogilvy "Minitman" wrote: 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 On Wed, 19 Jul 2006 09:47:02 -0700, Tom Ogilvy wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row Using 3 ComboBoxes As Criteria
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? On Wed, 19 Jul 2006 12:08:01 -0700, Tom Ogilvy wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row Using 3 ComboBoxes As Criteria
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" wrote: 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? On Wed, 19 Jul 2006 12:08:01 -0700, Tom Ogilvy wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row Using 3 ComboBoxes As Criteria
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 On Wed, 19 Jul 2006 13:04:01 -0700, Tom Ogilvy wrote: format(c.offset(0,1).Value2, "mm/dd/yyyy hh:mm AM/PM") = _ format(cdate(cb2.Value)),"mm/dd/yyyy hh:mm AM/PM") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the last row using 2 to 20 criteria | Excel Worksheet Functions | |||
EXCEL - Meet 2 criteria, then find next case of third criteria | Excel Worksheet Functions | |||
Where is "open/tools/find/find files that match these criteria"? | Excel Discussion (Misc queries) | |||
Trying to find a max value with criteria | Excel Worksheet Functions | |||
Find with criteria | Excel Programming |