Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky Macro Coding Question
I have a macro that churns down through a column of dates ("For Each cel in
Selection...") and accumulates (count and sum) certain information from that row. When the macro reaches the end of a month, I need it to transfer the accumulated values to another area of the spreadsheet, then zero out the "accumulator" cells and start over again for the new month. My problem is that the area the accumulated values are moved to is itself a range corresponding to months; if the accumulated values are for January I need to put them under January, if they're for April they go under April, and so on. I can do that with another "For Each cel" selection routine, but that breaks the connection to the ORIGINAL selection, causing me to in effect "lose my place" in that column of dates. Can anybody suggest how to move the accumulated values to the correct position in my months range while remembering where I was in the dates range and resuming the process from that point? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky Macro Coding Question
You could probably do what you want if instead of the For Each...Next loop
you would use: For i = (beginning row number) To (ending row number) 'substitute your values If Cells(i, ColNbr) = (month) Then 'copy data range to appropriate location, etc. End If Next i That way you could tell where i is because it moves from top to bottom sequentially. However, if your source range is not contained in one column, this would not work. "LarryP" wrote: I have a macro that churns down through a column of dates ("For Each cel in Selection...") and accumulates (count and sum) certain information from that row. When the macro reaches the end of a month, I need it to transfer the accumulated values to another area of the spreadsheet, then zero out the "accumulator" cells and start over again for the new month. My problem is that the area the accumulated values are moved to is itself a range corresponding to months; if the accumulated values are for January I need to put them under January, if they're for April they go under April, and so on. I can do that with another "For Each cel" selection routine, but that breaks the connection to the ORIGINAL selection, causing me to in effect "lose my place" in that column of dates. Can anybody suggest how to move the accumulated values to the correct position in my months range while remembering where I was in the dates range and resuming the process from that point? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky Macro Coding Question
Okay, makes sense; I'll give it a go. Thanks!
"JLGWhiz" wrote: You could probably do what you want if instead of the For Each...Next loop you would use: For i = (beginning row number) To (ending row number) 'substitute your values If Cells(i, ColNbr) = (month) Then 'copy data range to appropriate location, etc. End If Next i That way you could tell where i is because it moves from top to bottom sequentially. However, if your source range is not contained in one column, this would not work. "LarryP" wrote: I have a macro that churns down through a column of dates ("For Each cel in Selection...") and accumulates (count and sum) certain information from that row. When the macro reaches the end of a month, I need it to transfer the accumulated values to another area of the spreadsheet, then zero out the "accumulator" cells and start over again for the new month. My problem is that the area the accumulated values are moved to is itself a range corresponding to months; if the accumulated values are for January I need to put them under January, if they're for April they go under April, and so on. I can do that with another "For Each cel" selection routine, but that breaks the connection to the ORIGINAL selection, causing me to in effect "lose my place" in that column of dates. Can anybody suggest how to move the accumulated values to the correct position in my months range while remembering where I was in the dates range and resuming the process from that point? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro/VB coding question | Excel Discussion (Misc queries) | |||
Tricky Macro Question | Excel Worksheet Functions | |||
New guy with a tricky question | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming | |||
Tricky Question | Excel Worksheet Functions |