NEED HELP WITH LOOP !

  • Thread starter Thread starter flex zax
  • Start date Start date
F

flex zax

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~
 
Hi

I've changed the process somewhat. This assumes that the data will start in cell D4, D54 etc and will go down the same 39 rows. Output is on row 44, 94 etc. It is will keep looping as long as there is data in cell D4, D54 etc.

Hope this helps.

Tony

Sub ddd()
Dim arr(9)
Range("d4").Select
While Not IsEmpty(ActiveCell)
For i = 0 To 9
arr(i) = 0
Next i

For i = 1 To 39
If Month(ActiveCell) = Month(Now()) Then
For j = 0 To 9
arr(j) = arr(j) + ActiveCell.Offset(0, j + 1)
Next j
End If
ActiveCell.Offset(1, 0).Select
Next i

For i = 0 To 9
ActiveCell.Offset(1, i + 1).Value = arr(i)
Next i
ActiveCell.Offset(11, 0).Select
Wend

End Sub
 
Tony-
You are a super-genius !! Thank U!!!
I have a 3 questions about your code-

1. If I wanted it to calculate the data for last month instead. How do I
modify the Month(Now()) in the line

... If Month(ActiveCell) = Month(Now())...?
I tried
If Month(ActiveCell) = MONTH(DATE(YEAR(NOW()),MONTH(NOW()),0))...
but it gave me a compile error.

2. Second, is there a way to add code to a VBA code to dictate how slow
or fast it can run?

3. Can you please briefly comment your code for me so I can understand
what's going on better? I am a beginner. Also, is there a VBA textbook
you would recommend that I purchase to help me?

Thanks.
~Flex~
 
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
 
Back
Top