|
Macro too long
Hi, is there a way to do the same thing that is doing this code, but in more general way because this is taking the 64K availables in th 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 = " -- John2 ----------------------------------------------------------------------- John21's Profile: http://www.excelforum.com/member.php...fo&userid=3698 View this thread: http://www.excelforum.com/showthread.php?threadid=57143 |
Macro too long
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 |
Macro too long
Dim rng as Range, cell as Range
Dim RngColD as Range, i as Range Dim Info as String set rng = Range(Range("A493"),Range("493").End(xldown)) Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp)) for each cell in rng Info = "" For Each i In RngColD If i.Value = cell.Value Then If Info = "" Then Info = i.Offset(, 20).Value Else Info = Info & "," & i.Offset(, 20).Value End If End If Next i cells(cell.row,"S").Value = Info Next Cell -- Regards, Tom Ogilvy ohn21" wrote: 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 |
Macro too long
set rng = Range(Range("A493"),Range("493").End(xldown))
should be set rng = Range(Range("A493"),Range("A493").End(xldown)) -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: Dim rng as Range, cell as Range Dim RngColD as Range, i as Range Dim Info as String set rng = Range(Range("A493"),Range("493").End(xldown)) Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp)) for each cell in rng Info = "" For Each i In RngColD If i.Value = cell.Value Then If Info = "" Then Info = i.Offset(, 20).Value Else Info = Info & "," & i.Offset(, 20).Value End If End If Next i cells(cell.row,"S").Value = Info Next Cell -- Regards, Tom Ogilvy ohn21" wrote: 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 |
| All times are GMT +1. The time now is 12:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com