I have another question. The code searches all the five numbers 7 40 61 62 68
in the first row (1-Sep) till the last row. If I want to search any 4 numbers
out of these five ([7, 40, 61, 62] or [7, 40, 61, 68] or [7, 40, 62, 68] or
[7, 61, 62, 68] or [40, 61, 62, 68]), what alterations will I have to do?

I tried to modify the code but I am not sure what I am doing wrong. I
understood half of the code but did not understand why you kept "cnt" and the
line "res = Application.Match(v(1, k), v1, 0)"

Please help

Maxi

"Tom Ogilvy" wrote:

I don't mean to sound ungrateful for the opportunity to try to solve your
problem, but why bother to put up an example that doesn't match your
situation? One way to modify the code would be to sit down and figure out
how it works. Then make the necessary alterations. Nonetheless, this
should work for labels in Row 1 and results starting in AS2

Option Explicit
Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Set tbl = Range(Cells(2, 3), _
Cells(2, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(2, "y"), _
Cells(2, "y").End(xlDown)).Resize(, 5)
For i = 1 To tbl1(tbl1.Count).Row
icol = 45 ' AE is 31, AS is
v = Cells(i, 25).Resize(1, 5).Value
For j = 1 To tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt < k Then Exit For
Next k
If cnt = 5 Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"mac_see" wrote in message
...
Works fine.

If I insert one row on top to put the headings, the vba does not work???

Also what changes I will have to make to start the result from column "AS"
instead of "AE"

Thanx
Maxi

"Tom Ogilvy" wrote:

Option Explicit
Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Set tbl = Range(Cells(1, 3), _
Cells(1, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(1, "y"), _
Cells(1, "y").End(xlDown)).Resize(, 5)
For i = 1 To tbl1(tbl1.Count).Row
icol = 31
v = Cells(i, 25).Resize(1, 5).Value
For j = 1 To tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt < k Then Exit For
Next k
If cnt = 5 Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy