I'm trying to set something up so a user can make their criteria selection via data validation lists. In my example, their choice is Car, Red, UK (vehicle, colour, country). Clicking this searches through rows in column A, D and E, it then returns the corresponding row's B value into J3 and the corresponding row's C column into I3.
I tried doing this with Match but apparently you can't use that to cycle through results, only to find 1 match.
I found the following code and adapted it to my data:
[/FONT][/COLOR] Dim lLoop As Long Dim rFoundCell As Range choice = Range("H6").Value With Range("A:A") Set rFoundCell = .Cells(1, 1) For lLoop = 1 To WorksheetFunction.CountIf(.Cells, choice) Set rFoundCell = .find(What:=choice, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) MsgBox rFoundCell Next lLoop End With [FONT=Arial, Helvetica Neue, Helvetica, sans-serif][COLOR=#242729]
[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]This prompts a MsgBox to appear 3 times because the value of H6 is "Car" and "Car" appears 3 times in column A.[/FONT]
[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]My question is:
a) How can I adapt this to have 3 conditions, so rather than just looking for the value of H6, it has to find a row that contains H6, I6 and J6
b) Once it has found rows that match those 3 conditions, I need it to execute the found value once every click. This is because once it has found the row value, I'll add my own "B" and "C" to that row so that I can have the value of "B6" for example (if one of the finds return row 6) into I3/J3. But rather than it listing all of the results in one go, I want it to show a different result on every click (replacing the last)
Hope this makes sense, any help would be much appreciated![/FONT]