sum column with variable range in loop

[GaiGauci]

Hi all. I have been developing a macro to clean up a SAP download report. Now
I'm up to putting sub totals in column M at 11 different places. I am putting
the total at the top of the sub total range, in line with the subheadings.
The row where the sub totals will be will vary depending on the
entries/number of rows, so I need script that can accomodate this (ie. go to
the first empty cell in that column, total below it to the next empty cell
and then move on to the next subheading). I suppose it also needs to be
reflected in a loop to do the remaining 10 subtotals. I don't think a need a
grand total at the bottom.

Can anyone help me? I have been reading through other already posted scripts
(thanks Stefi and Ryan et al) and trying to modify but still seem to be going
wrong.The text in the subheading will remain constant if that is any help.

Thanks in advance,
Gai

 

[Stefi]

Your data layout was not quite clear for me, but maybe you can use this
sample (it goes to the first empty cell in column A, total below it to the
next empty cell then loops until the last empty cell in column A, in other
words it inserts SUBTOTAL above addends and not below them if that was what
you meant):

Sub stotal()
Range("A1").End(xlDown).Activate
Range("A" & ActiveCell.Row - 1).Activate
Do While ActiveCell.Row <> Range("A" & Rows.Count).End(xlUp).Row + 1
stotstart = ActiveCell.Row + 1
stotend = Range("A" & ActiveCell.Row + 1).End(xlDown).Row
ActiveCell.Formula = "=SUBTOTAL(9,A" & stotstart & ":A" & stotend &
")"
Range("A" & stotend + 1).Activate
Loop
End Sub

Regards,
Stefi

„GaiGauci†ezt írta:

 

[GaiGauci]

Thanks Stefi, that worked great! The only problem is once I went to carry it
out I realised that I had one more complication. To explain further, on the
right of the report, in columns S and T I have the totals for the lines. It
is these I wanted the sub totals to appear in at change of location. It
worked beautifully for each individually but I realised that they will have
other blanks in the columns that don't need a subtotal in. The sub total
needs to appear in both when both columns are empty. I then will need to
format these to be bold and 11 font.

Is there a way this formula can look at both columns to see if empty and
then subtotal each one?

I'm kind of guessing, so bare with me, but something like(?)....

Range("S1").End(xlDown).Activate
Range("S" & ActiveCell.Row - 1).Activate
Do While ActiveCell.Row <> Range("S:T" & Rows.Count).End(xlUp).Row + 1
stotstart = ActiveCell.Row + 1
stotend = Range("S" & ActiveCell.Row + 1).End(xlDown).Row
with selection.font
.font = 11
.bold = true
ActiveCell.Formula = "=subtotal(9,S" & stotstart & ":S" & stotend & ")"
Range("S" & stotend + 1).Activate
Nextstotstart = ActiveCell.Row + 1, ActiveCell.column +1
Nextstotend = Range("T" & ActiveCell.Row + 1).End(xlDown).Row
ActiveCell.Formula = "=subtotal(9,T" & nextstotstart & ":T" &
nextstotend & ")"
Range("S" & stotend + 1).Activate

Loop

What are your thoughts???

 

[Stefi]

Sorry for the late answer but I've just now realized that my e-mail
notification doesn't work.
Try this way:

Sub stotal()
Range("S1").End(xlDown).Activate
Range("S" & ActiveCell.Row - 1).Activate
Do While ActiveCell.Row <> Range("S" & Rows.Count).End(xlUp).Row + 1
stotstart = ActiveCell.Row + 1
stotend = Range("S" & ActiveCell.Row + 1).End(xlDown).Row
If IsEmpty(Range("T" & ActiveCell.Row)) Then
With ActiveCell
.Formula = "=SUBTOTAL(9,S" & stotstart & ":S" & stotend & ")"
.Font.Size = 11
.Font.Bold = True
End With
End If
Range("S" & stotend + 1).Activate
Loop
End Sub


--
Regards!
Stefi



„GaiGauci†ezt írta: