Hi everyone,
This should be an easy one. I have account numbers in column G, and invoice amounts in column F. I want to
<tbody>
</tbody>
I have gotten this far with the code here:
Any help is appreciated!
Thanks
This should be an easy one. I have account numbers in column G, and invoice amounts in column F. I want to
- Insert a new row once there is a new account number.
- Sub total the invoice amounts in that new row.
Invoice | Account |
$5 | 11111 |
$5 | 11111 |
$10 | 11111 |
$20 | Subtotal |
$3 | 22222 |
$3 | 22222 |
$3 | 22222 |
$3 | 22222 |
$12 | Subtotal |
<tbody>
</tbody>
I have gotten this far with the code here:
Code:
Sub InsertRowsAtValueChange()Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Worksheets("Billing").Range("G4:G1000")
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
'inserts blank row after new account #
WorkRng.Cells(i, 1).EntireRow.Insert
'Inserts text for subtotal
WorkRng.Cells(i, 1).Value = "Sub total"
'NEED FORMULA TO Sub Total each account
WorkRng.Cells(i, 0).Value = Application.SumIf(Range("g4:g1000"), Cells(i, 1), Range("F4:f1000"))
End If
Next
Application.ScreenUpdating = True
End Sub
Any help is appreciated!
Thanks