Using VBA, how can I SUM all the values from columns until I find column "Total"? Then do the same from next cell to next "total"

[Tpmola99]

I need to sum the values of each row and store them in the Total columns. the range of the hours columns can vary, they can be 4, 3... 20, 80... that's why it has to be detected automatically
The amount of "Hour" columns can change, so its not the same before every "Total" column. That`s why we need a automatic loop.
I have done all possible code to loop through the first line and find the Total columns but it doesn't come out any way.
Thanks for the help,

 

[Peter_SSs]

I empty the cell range content ..

• What cell range exactly?
• How are you emptying it? Manually or by macro?

 

[Tpmola99]

• What cell range exactly?
• How are you emptying it? Manually or by macro?

It would be from G2 to N, there is never a fixed range.

I am cleaning with:

ActiveSheet.Range("G2:DD180").ClearContents

 

[Peter_SSs]

It would be from G2 to N, there is never a fixed range.

I am cleaning with:

ActiveSheet.Range("G2:DD180").ClearContents

Starting at column G does not seem to gel with the image from post #1 where the numerical data started in column C.
Has the layout changed from that post?

Also, what happens if you use this instead

ActiveSheet.Range("G2:DD180").SpecialCells(xlConstants, xlNumbers).ClearContents

 

[Tpmola99]

Starting at column G does not seem to gel with the image from post #1 where the numerical data started in column C.
Has the layout changed from that post?

Also, what happens if you use this instead

ActiveSheet.Range("G2:DD180").SpecialCells(xlConstants, xlNumbers).ClearContents

It is the code adapted to my excel sheet (for work), but it is the same. In the pors uno image, the range would be C1-> DDD180, it is not always fixed.

I have tried that and the formula still does not stay in the Total cell so that it adds up as I put the hours by hand

 

[Peter_SSs]

the formula still does not stay in the Total cell

The code I suggested ..

ActiveSheet.Range("G2:DD180").SpecialCells(xlConstants, xlNumbers).ClearContents

.. will not clear any formulas from the range G2:DD180.
So if the formulas "do not stay in the Total cell" then there must be something else removing them.

 

[DanteAmor]

In the pors uno image, the range would be C1-> DDD180, it is not always fixed.

Try the following and let's see how it works for you.

The following macro summarizes from cell C1 onwards. Every time you add a value to the sheet, it will automatically recalculate the entire sheet, putting the totals under the "Total" column, it doesn't matter if you move the "Total" text to another column, the macro will do the job and always find the text " Total" to put the results.

In the sheet events put the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  Call SumTotalCh
  Application.EnableEvents = True
End Sub

Sub SumTotalCh()
  Dim a As Variant
  Dim i As Long, j As Long
  Dim tot As Double
  
  a = Range("C1", Cells(Range("A" & Rows.Count).End(3).Row, Cells(1, Columns.Count).End(1).Column)).Value
  
  For i = 2 To UBound(a, 1)
    tot = 0
    For j = 1 To UBound(a, 2)
      If a(1, j) = "Total" Then
        a(i, j) = tot
        tot = 0
      Else
        tot = tot + a(i, j)
      End If
    Next
  Next
  
  Range("C1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Redirecting
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.