Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro too long
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo Max Loger Excel Discussion (Misc queries) 0 June 14th 11 04:30 PM
Long Long Long Nested If Function sed Excel Discussion (Misc queries) 4 December 9th 09 06:44 PM
HELP - Too long coding for Macro Eric Excel Worksheet Functions 12 February 22nd 08 02:55 PM
How to solve too long coding for Macro ? Eric Excel Worksheet Functions 1 February 15th 08 12:48 PM
how do i add time to see how long my macro takes to run ernestgoh[_6_] Excel Programming 2 July 16th 06 11:05 AM


All times are GMT +1. The time now is 09:35 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement