# 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 (Dec 15, 2022)

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,


----------



## DanteAmor (Dec 15, 2022)

Hi and welcome to MrExcel!

Try this:

```
Sub SumTotal()
  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)
    For j = 1 To UBound(a, 2)
      tot = tot + a(i, j)
      If a(1, j) = "Total" Then
        a(i, j) = tot
        tot = 0
      End If
    Next
  Next
  
  Range("C1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub
```

----------------
*NOTE XL2BB:*
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: *XL2BB Add-in*
Note that there is also a "*Test Here*” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

----------------
Example:
Dante AmorABCDEFGHIJKLMNO1NameSurnameHour1Hour2Hour3TotalHour1Hour2Hour3Hour4Hour5TotalHour1Hour2Total2nm1sn135541353423nm2sn255241312234nm3sn355232525345nm4sn451455422156nm5sn543125121437nm6sn644313411338nm7sn743454414349nm8sn82534424333Sheet1

Result:
Dante AmorABCDEFGHIJKLMNO1NameSurnameHour1Hour2Hour3TotalHour1Hour2Hour3Hour4Hour5TotalHour1Hour2Total2nm1sn13551341353164263nm2sn25521241312112354nm3sn35521232525173475nm4sn45141055422181566nm5sn5431825121114377nm6sn64431113411103368nm7sn74341154414183479nm8sn8253104424317336Sheet1


----------



## Tpmola99 (Dec 15, 2022)

DanteAmor said:


> Hi and welcome to MrExcel!
> 
> Try this:
> 
> ...


Thank you very much for the reply!! I had been trying for days and couldn't. Thank you for all the information.
Now I have to duplicate this sheet and clean the content of the hours (I have this) but I have to leave the automatic addition as I insert the data.
It would be the same?

Thank you!


----------



## Peter_SSs (Dec 15, 2022)

Welcome to the MrExcel board!

I'm wondering if you want formulas in those 'Total' cells so that when you duplicate the sheet and remove the hours content, the formulas will remain ready for new data insertion?

If so, perhaps you could try this with a *copy *of your workbook.


```
Sub Total_Formulas()
  Dim rTotal As Range
  Dim x As Long, fc As Long, lr As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Set rTotal = Rows(1).Find(What:="Total")
  x = rTotal.Column
  fc = 3
  Do
    rTotal.Offset(1).Resize(lr - 1).FormulaR1C1 = "=SUM(RC" & fc & ":RC[-1])"
    fc = rTotal.Column + 1
    Set rTotal = Rows(1).Find(What:="Total", After:=rTotal)
  Loop Until rTotal.Column = x
End Sub
```


----------



## DanteAmor (Dec 15, 2022)

Tpmola99 said:


> Now I have to duplicate this sheet and clean the content of the hours (I have this) but I have to leave the automatic addition as I insert the data.
> *It would be the same?*


I do not understand your question.
Just do the test, duplicate your sheet, start putting the hours, and finally run my macro again, it will calculate all the totals in a second.


----------



## Tpmola99 (Dec 16, 2022)

DanteAmor said:


> I do not understand your question.
> Just do the test, duplicate your sheet, start putting the hours, and finally run my macro again, it will calculate all the totals in a second.


The addition should be automatic by putting values in the hours and not running the macro again


----------



## Peter_SSs (Dec 16, 2022)

Tpmola99 said:


> The addition should be automatic by putting values in the hours and not running the macro again


.. so did you try my suggestion?


----------



## Tpmola99 (Dec 16, 2022)

Peter_SSs said:


> .. so did you try my suggestion?


Thank you very much for the answer!!
Yes, I've tried it and I can't get it to work


----------



## Peter_SSs (Dec 16, 2022)

Tpmola99 said:


> I can't get it to work


Can you explain what the problem is?


----------



## Tpmola99 (Dec 16, 2022)

Peter_SSs said:


> Can you explain what the problem is?


Yeah! When I duplicate the sheet, I empty the cell range content and insert the code provided by you. The cells remain clean but if I insert values, the sum is not done.

Thanks for the answer, I'm new in this world.


----------



## Tpmola99 (Dec 15, 2022)

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 (Dec 16, 2022)

Tpmola99 said:


> I empty the cell range content ..



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


----------



## Tpmola99 (Dec 16, 2022)

Peter_SSs said:


> *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 (Dec 16, 2022)

Tpmola99 said:


> It would be from G2 to N, there is never a fixed range.
> 
> I am cleaning with:
> 
> ...


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 (Dec 16, 2022)

Peter_SSs said:


> 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
> ...


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 (Dec 16, 2022)

Tpmola99 said:


> 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 (Dec 16, 2022)

Tpmola99 said:


> 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 (Dec 17, 2022)

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.


----------

