SUM Macro

[The_Rock]

Hi Guys
I'm trying to create a SUM Macro that will sum the total in a number of columns.

The number of rows and columns are not fixed and change from week to week.

Currently, this is the code I have

Range("J65536:AE65536").End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R5C:R[-2]C)"

This brings back the total for the first column (j) but does not return any results for anything else.

I entered the AE65536 to see if I made the column an absolute, whether results would be returned but no luck.

Any advice would be greatly appreciated.

 

[golfbum71]

Hello,

Someone may have a more elegant solution, but this may work...

Sub SetSums()

FinalRow = Range("J65536").End(xlUp).Row + 2
For x = 0 To 21
    Range("J" & FinalRow).Offset(0, x).FormulaR1C1 = "=SUM(R5C:R[-2]C)"
Next x

End Sub

I'm big into loops...

Cheers,
Geoff

 

[pgc01]

Hi

The number of rows and columns are not fixed and change from week to week.

Your formula only writes one cell. If you want to write more cells you have to resize it.

You calculated the row number but you have also to calculate the number of columns.

I used row 5 to get the number of columns since it's the first row in your formula. Adjust if necessary.

Sub TableTotals()

Dim lColumns As Long, lRow As Long

lRow = Cells(Rows.Count, "J").End(xlUp).Offset(2).Row
lColumns = Range("J5", Cells(5, Columns.Count).End(xlToLeft)).Columns.Count

Cells(lRow, "J").Resize(1, lColumns).FormulaR1C1 = "=SUM(R5C:R[-2]C)"
End Sub

Hope this helps
PGC

 

[golfbum71]

Good catch,

And thanks for the lesson...
Resize isn't something I use...have to learn.

Cheers,
Geoff

 

[The_Rock]

Excellent, thanks for that, worked like a treat.
One thing I noticed was that if I used "Cells(5,", it only bought back the results for 10 columns.

I changed the limit to "Cells(3," and now it calculates for all regardless the number of columns.

Thanks again guys

 

[The_Rock]

Another question on this. What coding would I need to use to bold these totals?

 

[pgc01]

Hi again

You can just add before the End Sub

Cells(lRow, "J").Resize(1, lColumns).Font.Bold = True

Hope this helps
PGC

 

[The_Rock]

Thanks pgc01

I'm applying this macro to 8 tabs in total.

I'm not very good with writing coding but can this be looped through out the spreadsheet?

The only way that I have been able to get around it is to change the letter in the dim statement for e.g.

Sheet 1
Dim lColumns As Long, lRow As Long
lRow = Cells(Rows.Count, "J").End(xlUp).Offset(2).Row
lColumns = Range("J5", Cells(3, Columns.Count).End(xlToLeft)).Columns.Count

Cells(lRow, "J").Resize(1, lColumns).FormulaR1C1 = "=SUM(R5C:R[-2]C)"

Sheet 2
Dim kColumns As Long, kRow As Long
kRow = Cells(Rows.Count, "J").End(xlUp).Offset(2).Row
kColumns = Range("J5", Cells(3, Columns.Count).End(xlToLeft)).Columns.Count

Cells(kRow, "J").Resize(1, kColumns).FormulaR1C1 = "=SUM(R5C:R[-2]C)"

etc....
Is there a quicker way to do this?

 

[pgc01]

Hi

You can loop through the worksheets. Ex., for the first 3 worksheets of the workbook:

Sub TableTotals()
Dim lColumns As Long, lRow As Long, lWsh As Long

For lWsh = 1 To 3
    With Worksheets(lWsh)
        lRow = .Cells(.Rows.Count, "J").End(xlUp).Offset(2).Row
        lColumns = .Range("J5", .Cells(5, .Columns.Count).End(xlToLeft)).Columns.Count
        
        .Cells(lRow, "J").Resize(1, lColumns).FormulaR1C1 = "=SUM(R5C:R[-2]C)"
        .Cells(lRow, "J").Resize(1, lColumns).Font.Bold = True
    End With
Next lWsh
End Sub

Remark: dont forget to qualify the ranges with the dot ".".

Hope this helps
PGC

 

[golfbum71]

If you're applying it to all worksheets, you can use

For lWsh = 1 To worksheets.count

If you're applying it to select worksheets in a large file with many tabs, you can use an array to provide the names for the worksheets (rather than trying to figure out what the sheet number is for each)...

Sub TableTotalsArray()
Dim lColumns As Long, lRow As Long, lWsh As Long
Dim myAry As Variant

myAry = Array(, "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8")
For lWsh = 1 To UBound(myAry)
    With Worksheets(myAry(lWsh))
        lRow = .Cells(.Rows.Count, "J").End(xlUp).Offset(2).Row
        lColumns = .Range("J5", .Cells(5, .Columns.Count).End(xlToLeft)).Columns.Count
        
        .Cells(lRow, "J").Resize(1, lColumns).FormulaR1C1 = "=SUM(R5C:R[-2]C)"
        .Cells(lRow, "J").Resize(1, lColumns).Font.Bold = True
    End With
Next lWsh
End Sub

PGC, Thanks for the lessons...

Cheers,
Geoff