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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,381
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
 
Solution

Loading___

New Member
Joined
May 27, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,926
Messages
5,703,211
Members
421,282
Latest member
hogie

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