SUM Macro

The_Rock

Board Regular
Joined
Jul 2, 2007
Messages
174
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

Code:
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

golfbum71

Board Regular
Joined
Aug 6, 2007
Messages
123
Hello,

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

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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.

Code:
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

Board Regular
Joined
Aug 6, 2007
Messages
123
Good catch,

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

Cheers,
Geoff
 

The_Rock

Board Regular
Joined
Jul 2, 2007
Messages
174

ADVERTISEMENT

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 :biggrin:
 

The_Rock

Board Regular
Joined
Jul 2, 2007
Messages
174
Another question on this. What coding would I need to use to bold these totals?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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

Board Regular
Joined
Jul 2, 2007
Messages
174
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

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

Code:
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

Board Regular
Joined
Aug 6, 2007
Messages
123
If you're applying it to all worksheets, you can use

Code:
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)...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,950
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top