Macro to leave a formula in Total?

Status
Not open for further replies.

Tpmola99

New Member
Joined
Dec 15, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I need to sum the row across the "Total" column, but I can't get any code. The range of Hour1... It can vary and sometimes be more or less (That's why you have to look for the Total columns (Automatically) in row 1 and put in all the rows of the column to go adding the values that you insert by hand in the cells of Hour1, Hour2...
1671180990700.png

Does anyone know how to do it?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
VBA Code:
Sub herewegoagain()
        Dim k, i, j As Integer
        Dim lr, lc As Long
        Dim wk As Worksheet
        Set wk = ActiveSheet
       
        lr = wk.Cells(Rows.Count, 1).End(xlUp).Row
        lc = wk.Cells(1, Columns.Count).End(xlToLeft).Column
            For k = 2 To lr
                j = 3
                For i = 3 To lc
                        If Cells(1, i) = "Total" Then
                            Cells(k, i) = WorksheetFunction.Sum(Range(Cells(k, j), Cells(k, i - 1)))
                               j = i + 1
                        End If
                Next i
            Next k
End Sub
 

Attachments

  • 1671188621387.png
    1671188621387.png
    22 KB · Views: 4
Upvote 0
VBA Code:
Sub herewegoagain()
        Dim k, i, j As Integer
        Dim lr, lc As Long
        Dim wk As Worksheet
        Set wk = ActiveSheet
      
        lr = wk.Cells(Rows.Count, 1).End(xlUp).Row
        lc = wk.Cells(1, Columns.Count).End(xlToLeft).Column
            For k = 2 To lr
                j = 3
                For i = 3 To lc
                        If Cells(1, i) = "Total" Then
                            Cells(k, i) = WorksheetFunction.Sum(Range(Cells(k, j), Cells(k, i - 1)))
                               j = i + 1
                        End If
                Next i
            Next k
End Sub
This is the case if the data is already entered, but if I enter it by hand, it does not add up automatically, how would the formula be left so that the total is updated as I modify it?

Thank you!
 
Upvote 0
This is the case if the data is already entered, but if I enter it by hand, it does not add up automatically, how would the formula be left so that the total is updated as I modify it?

Thank you!
Do you know how to create a private sub routine?
 
Upvote 0
Select the sheet from the left side which is circled and double click it
do exactly what i have done
 

Attachments

  • 1671189909942.png
    1671189909942.png
    92.4 KB · Views: 4
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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