Total a range of cells macro

[Sunline]

Hello all , i have this macro which looks at col S (numbers) and totals a range of cells in another 3 x cols .
How its suppossed to work is in col S there are numbers from 1 to 24 , in cell S2 there is number 9 , that means there will be 9 rows containing #9 down too col S10 , next # is 8 so #8 down to S18 , next # is 14 so down too S32 and so on , there could be the same number mentioned 2 or more times , it varies over the 170,00 rows .

This macro which has stopped working then looks over too cols W,Y,AA , as with the first example in S2 being 9 will total cell range W2 too AA10 then on too next being #8 and so on .

Im hoping too get this working again after i stuffed it up , hopefully answer too go into col BO .
Also i want to do exactly the same as above but for only 1 col too total range , answer into col BO .

Sub SomeSortOfSubTotaling()
Dim WS As Worksheet
Dim LR As Long
Dim i As Long
Dim RptCount As Long
Dim MySum As Double
Dim Myrange As Range
Set WS = ActiveSheet
LR = WS.Range("S" & Rows.Count).End(xlUp).Row

i = 2
Do
If i > LR Then Exit Do
If RptCount = 0 Then
RptCount = WS.Cells(i, 20)
Set Myrange = WS.Range("W" & i & ":W" & i + RptCount - 1 _
& ",Y" & i & ":Y" & i + RptCount - 1 _
& ",AA" & i & ":AA" & i + RptCount - 1)
MySum = Application.WorksheetFunction.Sum(Myrange)

End If
WS.Cells(i, 21) = MySum
i = i + 1
RptCount = RptCount - 1
Loop
End Sub
Thankyou .

 

[ukmikeb]

Hi

Are you sure you are referencing S2 to find the number of repeated rows?

Then having completed for the value in S2 are you referencing S11 to find the next number of repeated rows?

May I suggest that you put in "WS.Cells(i, 20).Select" just before
"RptCount = WS.Cells(i, 20)" to discover which cell you are pointing to.

Try stepping through your code.

hth

 

[Sunline]

Thanks Mike for that , i did what you said and macro just keeps running so had to ctrl-alt-delete too intercept .
Col S is definately where the numbers i mentioned are , hopeing someone could do a change to this so i could perform a single count range total to col BN .
Thanks .

 

[ukmikeb]

I did what you said and macro just keeps running so had to ctrl-alt-delete too intercept .

No, you didn't set a breakpoint by clicking the bar to the left of the code on the "RptCount = WS.Cells(i, 20)" line.

Col S is definately where the numbers i mentioned are

That's why I asked you to put in the Select because I don't think you are pointing to the right column.

hopeing someone could do a change to this so i could perform a single count range total to col BN .

If you get someone else to do the change for you, you will not learn from your mistakes.

Am I correct in assuming that you wish to obtain totals for those three columns for each reducing number of rows (9,8,7...etc) starting in S2 or are you expecting one total for the group starting in S2 and then one total for the group starting in S10...etc?

hth

 

[Sunline]

yes i do want too obtain a total from those 3 cols .
S2 9
S3 9
S4 9
S5 9
S6 9
S7 9
S8 9
S9 9
S10 9
Next # is 8 , same as above down col S .
Total all cells in cols W2 too W10 , Y2 too Y10 , AA2 too AA10 and so on
The numbers arnt reducing in col S , it could be next 16 , 4 , 18 , 10 , 11 , 10 , 10 , 10 , 5 , 13 , etc . very random .
Thanks .

 

[ukmikeb]

Hi

Let's assume that the values in each of the columns for the first set of 9 is 1 through 9 which gives a total of 45 for each column and 135 for all three columns.
For the next 8 rows of the same 9 set the total for each column will be 44 and 132 for all three columns.
For the next 7 rows of the same 9 set the total for each column will be 42 and 126 for all three columns.

You need to make sure that you are referencing the correct column "S" when you set the initial value for RptCount (cell "S2").

Use the Debug facility in vba to check that the code is progressing correctly and the values for the variables are what you expect.

If what I have suggested for the totals above is what you want, you need to make a minor change to the logic in your code. I have indented the code below to help you on your way.

Sub SomeSortOfSubTotaling()
Dim WS As Worksheet
Dim LR As Long
Dim i As Long
Dim RptCount As Long
Dim MySum As Double
Dim Myrange As Range
  
Set WS = ActiveSheet
LR = WS.Range("S" & Rows.Count).End(xlUp).Row
i = 2
 
Do
    If i > LR Then Exit Do
 
    If RptCount = 0 Then
         RptCount = WS.Cells(i, 20)
         Set Myrange = WS.Range("W" & i & ":W" & i + RptCount - 1 _
                               & ",Y" & i & ":Y" & i + RptCount - 1 _
                               & ",AA" & i & ":AA" & i + RptCount - 1)
         MySum = Application.WorksheetFunction.Sum(Myrange)
    End If
 
WS.Cells(i, 21) = MySum
 
i = i + 1
RptCount = RptCount - 1
 
Loop
 
End Sub

hth

 

[Sunline]

Thanks Mike for your patience and work for me , i never wrote the original macro , i am still learning functions . Basic learner .
The macro is running but all i getting is col U is being filled with the number 8 .
I dont know what to do refering to Debug .
Thanks .

 

[ukmikeb]

Hi

For Debug, with the VBE click the vertical bar next to the line of code and that line will then be coloured Brown.

If you then run the code by clicking the > (Run) in the bar next to the Undo/Redo changes buttons the code will then run and stop at the line you clicked previously.

This time the line will be highlighted in Yellow.

Now, if you hover the cursor over one of your variables on one of the other lines it will display the current value of that variable.

Click the vertical bar against further lines of code to trace where the code is going and click > (Run).

I suggest you change the cell references to "S" and "BO" from 20 and 21 (U).

hth

 

[Sunline]

ok thanks for your help Mike i will follow these steps to see if i can fix this , thanks again .