Nice logic....... and I was thinking of using countif instead of match

Thanx a million

Maxi

"Tom Ogilvy" wrote:

Set NumMatch to the number of matches

Right now it would do 4 or greater with NumMatch = 4

if you want exactly 4 (exclude 5 matches) then change

If cnt = NumMatch Then

to
If cnt = NumMatch Then

But I would assume you want 4 or greater.

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
Dim NumMatch As Long, diff As Long
'
NumMatch = 4
'
diff = 5 - NumMatch
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 = 2 To tbl1(tbl1.Count).Row
icol = 45 ' AE is 31, AS is 45
v = Cells(i, 25).Resize(1, 5).Value
For j = 2 To tbl(tbl.Count).Row
Debug.Print i, j, 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 = NumMatch Or cnt < k - diff Then Exit For
Next k
If cnt = NumMatch 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
...
I know that either I have to do something to this line "res =
Application.Match(v(1, k), v1, 0)" or use countif worksheet function but I
am
not sure how to use it.

Tom, I tried my best to manipulate this code continuously for 4 days with
no
luck. Can you please help?

Maxi

"mac_see" wrote:

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