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?

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?

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?