Adding a sum formula to worksheets with variable rows

BrennieB

New Member
Joined
Sep 28, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
This question is compound, so I need to sum up the beginning before I get to my question. I have a spreadsheet that is being split into multiple workbooks based on a column. Once this is done, I need to sum...let's say Column K in each worksheet at the last row in that column +2. I need the formula to show though, not the sum. I can not get the syntax correct. Any help would be appreciated. This is what I am working with (forgive the sloppy language, I am new at VBA) I have only included the part in which I am trying to fix...not the part before that splits it into workbooks:

lastrow = objSheet.Cells(Rows.Count, 11).End(xlUp).Row
lastrow1 = objSheet.Cells(Rows.Count, 14).End(xlUp).Row
lastrow2 = objSheet.Cells(Rows.Count, 15).End(xlUp).Row
lastrow3 = objSheet.Cells(Rows.Count, 16).End(xlUp).Row
lastrow4 = objSheet.Cells(Rows.Count, 17).End(xlUp).Row
lastrow5 = objSheet.Cells(Rows.Count, 18).End(xlUp).Row
objSheet.Range("J" & lastrow + 2) = "Total:"
objSheet.Range("J" & lastrow + 2).Font.Bold = True
objSheet.Range("K" & lastrow + 2) = Application.WorksheetFunction.Sum(objSheet.Range("K2:K" & lastrow)) This is where I am trying to produce the formula in the worksheet, not the sum
objSheet.Range("K" & lastrow + 2).Font.Bold = True
objSheet.Range("K" & lastrow + 2).NumberFormat = "$#,##0.00"
objSheet.Range("N" & lastrow1 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("N2:N" & lastrow1))
objSheet.Range("N" & lastrow1 + 2).Font.Bold = True
objSheet.Range("N" & lastrow1 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("O" & lastrow2 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("O2:O" & lastrow2))
objSheet.Range("O" & lastrow2 + 2).Font.Bold = True
objSheet.Range("O" & lastrow2 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("P" & lastrow3 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("P2:P" & lastrow3))
objSheet.Range("P" & lastrow3 + 2).Font.Bold = True
objSheet.Range("P" & lastrow3 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("Q" & lastrow4 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("Q2:Q" & lastrow4))
objSheet.Range("Q" & lastrow4 + 2).Font.Bold = True
objSheet.Range("Q" & lastrow4 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("R" & lastrow5 + 2) = Application.WorksheetFunction.Sum(objSheet.Range("R2:R" & lastrow5))
objSheet.Range("R" & lastrow5 + 2).Font.Bold = True
objSheet.Range("R" & lastrow5 + 2).NumberFormat = "$#,##0.00"
objSheet.Range("A:S").AutoFilter
objSheet.Name = objSheet.Range("A2")
Cells.SpecialCells(xlCellTypeVisible)(1).Select
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,845
Welcome to the MrExcel forum!

Try:

VBA Code:
objSheet.Range("K" & lastrow + 2).Formula = "=Sum(K2:K" & lastrow & ")"
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,223
Messages
5,768,902
Members
425,501
Latest member
sunderlalrwr

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