Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))

For j = 494 To 497
Info = ""
For Each i In RngColD
If i.Value = Range("A" & j).Value Then
If Info = "" Then
Info = i.Offset(, 20).Value
Else
Info = Info & "," & i.Offset(, 20).Value
End If
End If
Next i
Range("S" & j) = Info
Next j


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"John21" wrote in
message ...

Hi,

is there a way to do the same thing that is doing this code, but in a
more general way because this is taking the 64K availables in the
macro


Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A493").Value Then
If Info = "" Then
Info = i.Offset(, 20).Value
Else
Info = Info & "," & i.Offset(, 20).Value
End If
End If
Next i
Range("S493") = Info
Info = ""

Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A494").Value Then
If Info = "" Then
Info = i.Offset(, 20).Value
Else
Info = Info & "," & i.Offset(, 20).Value
End If
End If
Next i
Range("S494") = Info
Info = ""

Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A495").Value Then
If Info = "" Then
Info = i.Offset(, 20).Value
Else
Info = Info & "," & i.Offset(, 20).Value
End If
End If
Next i
Range("S495") = Info
Info = ""

Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A496").Value Then
If Info = "" Then
Info = i.Offset(, 20).Value
Else
Info = Info & "," & i.Offset(, 20).Value
End If
End If
Next i
Range("S496") = Info
Info = ""

Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A497").Value Then
If Info = "" Then
Info = i.Offset(, 20).Value
Else
Info = Info & "," & i.Offset(, 20).Value
End If
End If
Next i
Range("S497") = Info
Info = ""


--
John21
------------------------------------------------------------------------
John21's Profile:
http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=571430