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,

 

[DanteAmor]

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 Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Surname	Hour1	Hour2	Hour3	Total	Hour1	Hour2	Hour3	Hour4	Hour5	Total	Hour1	Hour2	Total
2	nm1	sn1	3	5	5		4	1	3	5	3		4	2
3	nm2	sn2	5	5	2		4	1	3	1	2		2	3
4	nm3	sn3	5	5	2		3	2	5	2	5		3	4
5	nm4	sn4	5	1	4		5	5	4	2	2		1	5
6	nm5	sn5	4	3	1		2	5	1	2	1		4	3
7	nm6	sn6	4	4	3		1	3	4	1	1		3	3
8	nm7	sn7	4	3	4		5	4	4	1	4		3	4
9	nm8	sn8	2	5	3		4	4	2	4	3		3	3
Sheet1

Result:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Surname	Hour1	Hour2	Hour3	Total	Hour1	Hour2	Hour3	Hour4	Hour5	Total	Hour1	Hour2	Total
2	nm1	sn1	3	5	5	13	4	1	3	5	3	16	4	2	6
3	nm2	sn2	5	5	2	12	4	1	3	1	2	11	2	3	5
4	nm3	sn3	5	5	2	12	3	2	5	2	5	17	3	4	7
5	nm4	sn4	5	1	4	10	5	5	4	2	2	18	1	5	6
6	nm5	sn5	4	3	1	8	2	5	1	2	1	11	4	3	7
7	nm6	sn6	4	4	3	11	1	3	4	1	1	10	3	3	6
8	nm7	sn7	4	3	4	11	5	4	4	1	4	18	3	4	7
9	nm8	sn8	2	5	3	10	4	4	2	4	3	17	3	3	6
Sheet1

 

[Tpmola99]

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 Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Surname	Hour1	Hour2	Hour3	Total	Hour1	Hour2	Hour3	Hour4	Hour5	Total	Hour1	Hour2	Total
2	nm1	sn1	3	5	5		4	1	3	5	3		4	2
3	nm2	sn2	5	5	2		4	1	3	1	2		2	3
4	nm3	sn3	5	5	2		3	2	5	2	5		3	4
5	nm4	sn4	5	1	4		5	5	4	2	2		1	5
6	nm5	sn5	4	3	1		2	5	1	2	1		4	3
7	nm6	sn6	4	4	3		1	3	4	1	1		3	3
8	nm7	sn7	4	3	4		5	4	4	1	4		3	4
9	nm8	sn8	2	5	3		4	4	2	4	3		3	3
Sheet1

Result:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Surname	Hour1	Hour2	Hour3	Total	Hour1	Hour2	Hour3	Hour4	Hour5	Total	Hour1	Hour2	Total
2	nm1	sn1	3	5	5	13	4	1	3	5	3	16	4	2	6
3	nm2	sn2	5	5	2	12	4	1	3	1	2	11	2	3	5
4	nm3	sn3	5	5	2	12	3	2	5	2	5	17	3	4	7
5	nm4	sn4	5	1	4	10	5	5	4	2	2	18	1	5	6
6	nm5	sn5	4	3	1	8	2	5	1	2	1	11	4	3	7
7	nm6	sn6	4	4	3	11	1	3	4	1	1	10	3	3	6
8	nm7	sn7	4	3	4	11	5	4	4	1	4	18	3	4	7
9	nm8	sn8	2	5	3	10	4	4	2	4	3	17	3	3	6
Sheet1

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]

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]

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]

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]

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

.. so did you try my suggestion?

 

[Tpmola99]

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

I can't get it to work

Can you explain what the problem is?

 

[Tpmola99]

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.