Dynamic Cells for Sum Function

Loading___

New Member
Joined
May 27, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Hope You are doing fine in this pandemic season.

So I try to make macro using vba where you can add after the data is input to the sheet

here's what it's look like when the data is input the first time

1623147762932.png


the button corresponding to the addition some of the cells. If I push the buttons, here's what it looks like

1623147833063.png


It works fine, so far so good.

but then the problem comes if i have multiple input like this

1623147924464.png


so after i sum the second time the total getting mixed all the way to the top.
How to set the code so that the sum will start right after this "--------" cell?
Here's the VBA code for this:

VBA Code:
Sub Total()

    Dim LastRow As Long, LastCol As Long, Total As Long
    Dim StartRow As Long
    Dim StartRange As Range
    Dim workingSheet As Worksheet
    Set workingSheet = ThisWorkbook.Sheets("Sheet1")
            
            With workingSheet
            
                LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                
                StartRow = .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row).End(xlUp).Row
                             
                .Cells(LastRow + 1, 5).Value = "Total: "
                .Cells(LastRow + 1, 6).Value = "=SUM(F" & StartRow + 2 & ":F" & LastRow & ")"
                .Cells(LastRow + 1, 7).Value = "=SUM(G" & StartRow + 2 & ":G" & LastRow & ")"
                .Cells(LastRow + 1, 8).Value = "=SUM(H" & StartRow + 2 & ":H" & LastRow & ")"
                .Cells(LastRow + 1, 10).Value = "=SUM(J" & StartRow + 2 & ":J" & LastRow & ")"
                .Cells(LastRow + 1, 11).Value = "=SUM(K" & StartRow + 2 & ":K" & LastRow & ")"
                .Cells(LastRow + 1, 12).Value = "=SUM(L" & StartRow + 2 & ":L" & LastRow & ")"
                .Cells(LastRow + 1, 13).Value = "=SUM(M" & StartRow + 2 & ":M" & LastRow & ")"
                
                .Cells(LastRow + 1, 1).Value = "---------"
                .Cells(LastRow + 1, 2).Value = "---------"
                .Cells(LastRow + 1, 3).Value = "---------"
                .Cells(LastRow + 1, 4).Value = "---------"
                .Cells(LastRow + 1, 9).Value = "---------"
                
                .Cells(LastRow + 2, 1).Value = "---------"
                .Cells(LastRow + 2, 2).Value = "---------"
                .Cells(LastRow + 2, 3).Value = "---------"
                .Cells(LastRow + 2, 4).Value = "---------"
                .Cells(LastRow + 2, 5).Value = "---------"
                .Cells(LastRow + 2, 6).Value = "---------"
                .Cells(LastRow + 2, 7).Value = "---------"
                .Cells(LastRow + 2, 8).Value = "---------"
                .Cells(LastRow + 2, 9).Value = "---------"
                .Cells(LastRow + 2, 10).Value = "---------"
                .Cells(LastRow + 2, 11).Value = "---------"
                .Cells(LastRow + 2, 12).Value = "---------"
                .Cells(LastRow + 2, 13).Value = "---------"
                
            End With
                        
End Sub

Sorry for the long post
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Give this a shot:

VBA Code:
Sub Total()
Dim lr As Long, ld As Long

    lr = Cells(Rows.Count, "A").End(xlUp).Row
    ld = Evaluate("AGGREGATE(14,6,ROW(A1:A" & lr & ")/(LEFT(A1:A" & lr & ")=""-""),1)")
    
    Range(Cells(lr + 1, "A"), Cells(lr + 2, "M")).Value = "---------"
    Cells(lr + 1, "E").Value = "Total:"
    Range(Cells(lr + 1, "F"), Cells(lr + 1, "M")).Formula = "=SUM(F" & ld + 1 & ":F" & lr & ")"
    Cells(lr + 1, "I") = "---------"
    
End Sub
 
Upvote 0
Solution
Hi Erik,

Thank you for your fast response, and I'm sorry for the late reply.

It's perfect, thank you for your help.

it's been a crazy week to find the right way and function.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top