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!