macro to search for a set of numbers in a row

[mini]

I have a set of 4 numbers. I need to go to each row in the spreadsheet,
search for 4 cells whose values matched these 4 numbers and then copy the
value of the cell right after these 4 cells to a table.
For example: the 4 numbers are 1, 2, 3, 4
In a row, I got: 10 30 100 3 5 1 2 3 4 20 40.....
I will have to copy 20 to a table and then continue to the next row.
I see we have a lot of examples searching for 1 value but for 4, I'm not
sure how to do it with a macro.
Thanks a lot,
mini

[Leith Ross]

Hello Mini,

Copy the code in this post,from 'Start Macro Code to 'End Macro Code, and paste it into a VBA module. I am including how to do this in case you haven't done this before. The macro LoadTable won't place the value following the match into the table. It will search all rows on the Active Worksheet for the 4 values. The code to load the table isn't included since you didn't provide any information on it. If you need help doing that, repost with more information about the table layout and worksheet it is on, or you can e-mail me at LeithRoss@aol.com for more assistance.

'Start Macro Code

Public Function Match4(ByVal Search_Row As Long, ByVal Value_1, ByVal Value_2, ByVal Value_3, ByVal Value_4) As Variant

'Finds the first matching instance of the 4 values given in a specified row
'and returns the value in the cell to the right of the Value_4.

Dim Cell As Range
Dim Counter As Long

For Each Cell In ActiveSheet.Range(Cells(Search_Row, 1), Cells(Search_Row, 256))
Select Case Cell.Value
Case Value_1, Value_2, Value_3, Value_4
Counter = Counter + 1
Case Else
Counter = 0
End Select
If Counter = 4 Then
Match4 = Cell.Offset(0, 1).Value
Exit Function
End If
Next Cell

'Return value is Empty if no Match is found

End Function

Public Sub LoadTable()

Dim Row As Long
Dim X

'Search all rows with data in Active Worksheet for the 4 values
For Row = 1 To ActiveSheet.UsedRange.Rows.Count
X = Match4(Row, Range("A4"), Range("A5"), Range("A6"), Range("A7"))
If Not IsEmpty(X) Then
'Code to place return value
'in the table goes here.
End If
Next Row

End Sub

'End Macro Code

Instructions for Adding a VBA Module to the Workbook:

1) Special keys like Control, and Alternate are abbreviated CTRL and ALT.
2) the plus "+" sign means the first key is still held down while pressing the second. Example CTRL + C
3) Place the cursor on the single quote of 'Start Macro Code above. Left Click and hold the mouse button down while moving the mouse to the line 'End Macro Code and stop. Release the mouse button. All the code lines should now be highlighted.
4) With the Workbook Open, press ALT + F11 to activate the Visual Basic Editor.
5) Press ALT + I to activate the Insert Menu options.
6) Press M to insert a VBA module into the Workbook.
7) Press CTRL + V to paste the code into the module.
8) Press CTRL + S to save the moduleand code in the Workbook.
9) Press ALT + Q to close the Visual Basic Editor and return to Excel.

Using the Match4 Macro:

The macro takes 5 arguments...
ReturnValue = Match4(row number to search, first value, second value, third value, fourth value)

The macro searchs for the first match of all 4 values in the row and returns the value of the cell immediately to the right of the cell holding the fourth value. If no match is found, the function returns an Empty value. The arguments can be numeric expressions or cell values. The Row Number must a number. The 4 values can be any variable type.

Sincerely,
Leith Ross