My loop below works PERFECTLY for the set range ONLY once. However, I
NEED it to loop down the sheet by offsetting each set variable by 50
rows down and repeating the same calculations (i.e 50 rows down). I
realize I need a nested loop but don't know how.Any modifications,
improvement or a better looping construct or help would help me greatly.

Sub calcmonthsales()

Dim oCell As Range
Set dee = Range("D4:D42") ' contains dates
Set sm0 = Range("E44:E44")'sm0-sm9:totals of col numbers
Set sm1 = Range("F44:F44")'
Set sm2 = Range("G44:G44")
Set sm3 = Range("H44:H44")
Set sm4 = Range("I44:I44")
Set sm5 = Range("J44:J44")
Set sm6 = Range("K44:K44")
Set sm7 = Range("L44:L44")
Set sm8 = Range("M44:M44")
Set sm9 = Range("N44:N44")

sm0.Value = 0 ' Initialize each total
sm1.Value = 0
sm2.Value = 0
sm3.Value = 0
sm4.Value = 0
sm5.Value = 0
sm6.Value = 0
sm7.Value = 0
sm8.Value = 0
sm9.Value = 0

'the loop calculates each total based on the date in "dee"
For Each oCell In dee
If Month(oCell) = Month(Now()) Then
sm0.Value = sm0.Value + oCell.Offset(0, 1).Value
sm1.Value = sm1.Value + oCell.Offset(0, 2).Value
sm2.Value = sm2.Value + oCell.Offset(0, 3).Value
sm3.Value = sm3.Value + oCell.Offset(0, 4).Value
sm4.Value = sm4.Value + oCell.Offset(0, 5).Value
sm5.Value = sm5.Value + oCell.Offset(0, 6).Value
sm6.Value = sm6.Value + oCell.Offset(0, 7).Value
sm7.Value = sm7.Value + oCell.Offset(0, 8).Value
sm8.Value = sm8.Value + oCell.Offset(0, 9).Value
sm9.Value = sm9.Value + oCell.Offset(0, 10).Value
End If
End If
Next oCell

Thanks
~Flex~


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Flex

In response to your questions.

1) What I would do is add a cell to your sheet which contains a date of the month / year that you want to calculate. So January 2004 would contain the entry 1/1/04, December 2003 would have 1/12/03 etc. You could pick any day in the month. Then change the code so that it looks for items with the same month / year as the required time period. Say you put the required date in cell A1. You could then generate a couple of variables which hold the month and year of that cell (mth = month(cells(1,1)) and yr = year(cells(1,1)). The line
If Month(ActiveCell) = Month(Now()) Then
would then become
if month(activecell) = mth and year(activecell) = yr then


2) Not that I know of. You could put in little loops that make it count, put comments in the bottom of the screen to give a count of the number of iterations, or put in breakpoints in the code which make it pause at points so that you can see where you are. Only other thing I could think of would be to overload your system with other items so that it is competing for CPU time. This would slow it down. Are you sure that you want to slow it down, or just want to see what it is doing when. If this is the case, then use breakpoints and / or step through the code.

3) I've commented the code. Hope there is enough in that to explain what is going on.

Tony


Sub ddd()
'Declare an array variable of size 10 to hold the progressive totals for each column
Dim arr(9)
'go to the start point
Range("d4").Select
'while the starting point is not empty, process
While Not IsEmpty(ActiveCell)
'initialise the array elements to 0
For i = 0 To 9
arr(i) = 0
Next i
'Process each of the 39 items in the section
For i = 1 To 39
'the month has to be the same as the current month
If Month(ActiveCell) = Month(Now()) Then
'increment the totals for each column
For j = 0 To 9
arr(j) = arr(j) + ActiveCell.Offset(0, j + 1)
Next j
End If
'select the next cell in the column
ActiveCell.Offset(1, 0).Select
Next i
'output the column totals to the total line
For i = 0 To 9
ActiveCell.Offset(1, i + 1).Value = arr(i)
Next i
'select the next starting point for new data
ActiveCell.Offset(11, 0).Select
Wend

End Sub