VBA to Total column D based on name column A

[rspalding]

Hi and thanks for the help.​

I have a list of names in column A and numbers in column D. I want to total up the numbers in column D after the last entry in column A or D and add the word total next to it in column C. See below please

Alvarez	Ramiro	07/26/2024	8.000
Total​	8.00
Balzan	Mariana	07/26/2024	8.000
Total​	8.00
Cardoza	Rey	07/25/2024	13.250
Cardoza	Rey	07/26/2024	13.500
Cardoza	Rey	07/27/2024	13.250
Total​	40.00
Chavez	Jean	07/24/2024	8.000
Chavez	Jean	07/25/2024	8.000
Chavez	Jean	07/26/2024	8.000
Dominguez	Julio	07/25/2024	8.000
Dominguez	Julio	07/26/2024	8.000

 

[Jeffrey Mahoney]

Please try this:

Sub AddTotals()
  Dim Cel As Range
  Dim Rng As Range
  Dim FirstRow As Long
  Dim LastRow As Long
  Dim X As Long
  
  Set Rng = Range(Range("D1"), Range("D" & Cells.Rows.Count).End(xlUp))
  FirstRow = Rng.Resize(1, 1).Row
  LastRow = Rng.Resize(1, 1).Offset(Rng.Rows.Count, 0).Row
  
  For X = LastRow To FirstRow + 1 Step -1
    If Cells(X, 1) & Cells(X, 2) <> Cells(X - 1, 1) & Cells(X - 1, 2) Then
      Cells(X, 1).EntireRow.Insert
      Cells(X, 1).Value = "Total"
      Cells(X, 1).HorizontalAlignment = xlRight
      Cells(X, 2).Value = Application.SumIfs(Range("D:D"), Range("A:A"), Cells(X - 1, 1), Range("B:B"), Cells(X - 1, 2))
      Cells(X, 2).NumberFormat = "#,##0.0"
      Range(Cells(X, 1), Cells(X, 2)).Font.Bold = True
    End If
  Next X
  
End Sub

 

[rspalding]

Jeff,

Thank you for your help. I did make a mistake in my info. The total should be in row "F" and the word "TOTAL in row "E".

 

[Jeffrey Mahoney]

New Code to move the totals

Sub AddTotals()
Dim Cel As Range
Dim Rng As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim X As Long

Set Rng = Range(Range("D1"), Range("D" & Cells.Rows.Count).End(xlUp))
FirstRow = Rng.Resize(1, 1).Row
LastRow = Rng.Resize(1, 1).Offset(Rng.Rows.Count, 0).Row

For X = LastRow To FirstRow + 1 Step -1
If Cells(X, 1) & Cells(X, 2) <> Cells(X - 1, 1) & Cells(X - 1, 2) Then
Cells(X, 1).EntireRow.Insert
Cells(X, 3).Value = "Total"
Cells(X, 3).HorizontalAlignment = xlRight
Cells(X, 4).Value = Application.SumIfs(Range("D:D"), Range("A:A"), Cells(X - 1, 1), Range("B:B"), Cells(X - 1, 2))
Cells(X, 4).NumberFormat = "#,##0.0"
Range(Cells(X, 3), Cells(X, 4)).Font.Bold = True
End If
Next X

End Sub

 

[rspalding]

Jeff,
Still not quite there. I made some changes but not getting a sum and deleing the number on the first line of name. The total should only be below.

LastName	FirstName		EffectiveDate		Hours
Alvarez	Ramiro		07/26/2024		8.000
				Total	0.0​
Cardoza	Rey		07/25/2024	Total	0.0​
Cardoza	Rey		07/26/2024		13.500
Cardoza	Rey		07/27/2024		13.250
				Total	0.0​
Chavez	Jean		07/24/2024	Total	0.0​
Chavez	Jean		07/25/2024		8.000
Chavez	Jean		07/26/2024		8.000
				Total	0.0​

 

[Jeffrey Mahoney]

So I assumed you were starting with a blank set of data with no subtotals. If that is not the case, then we'll have to adjust. The macro worked on the set of data as below.

Book1 20240813.xlsm
	A	B	C	D
1	Alvarez	Ramiro	7/26/2024	8
2	Balzan	Mariana	7/26/2024	8
3	Cardoza	Rey	7/25/2024	13.25
4	Cardoza	Rey	7/26/2024	13.5
5	Cardoza	Rey	7/27/2024	13.25
6	Chavez	Jean	7/24/2024	8
7	Chavez	Jean	7/25/2024	8
8	Chavez	Jean	7/26/2024	8
9	Dominguez	Julio	7/25/2024	8
10	Dominguez	Julio	7/26/2024	8
Sheet3

 

[rspalding]

Looks like this only with more names.

LastName	FirstName		EffectiveDate		Hours
Alvarez	Ramiro		07/26/2024		8.000
Cardoza	Rey		07/25/2024		13.250
Cardoza	Rey		07/26/2024		13.500
Cardoza	Rey		07/27/2024		13.250
Chavez	Jean		07/24/2024		8.000
Chavez	Jean		07/25/2024		8.000
Chavez	Jean		07/26/2024		8.000
Dominguez	Julio		07/25/2024		8.000
Dominguez	Julio		07/26/2024		8.000

 

[rspalding]

Jeff,
I made my sheet look like yours and it works except that the hours have some type of a link and won't total. If I retype the hours so the link is gone all works. Can you give me a code to delete the link please.

Thanks,

Robert

 

[Jeffrey Mahoney]

Can you show me what is in the cell. It looks like the cells are text maybe instead of numbers. Do you have XL2BB so you can post a visual of the result?

 

[rspalding]

No I don't have XL2BB.