=sum(R[-1]C:

[Irek1974]

Hi,
I have a problem with summing in VBA.
the part of the VBA code is :
range("B1").end(xlDown).offset(1,3)
in column "B" I have invoices numbers and in column "D" I have value of the invoices.
after the above code I need to write the code which sums the value of the invoices.
Unfortunatelly I can't use "range("B1").end(xlDown).offset(1,3).Formula ="=sum(R[-1]C:R2C) because :
1. previous part of the code of my macro divides all invoices into clients (in Column A there is the client) inserting 2 empty rows below last invoice for the client - for example:
- first invoice for Client A is in row 2, last one is in row 5
- there is two empty rows
- first invoice for Client B is in row 8, last in row 10
- first invoice for Client C is in row 13, last in row 21 , ect.
in this example formula =sum(R[-1]C:R2C) would work only for Client A but for others not
2. there will be different number of invoices (and rows) for each clients

is there any method to sum the value of the invoices for each Clients using VBA code ?

I hope I described it quite clearly
would anybody help Please ?
Irek

 

[Andrew Poulsom]

Have you tried Data|Subtotals? That doesn't require any code.

 

[xld]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim cell As Range
Dim sh As Worksheet

    With ActiveSheet
        
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        For i = 2 To LastRow + 1
        
            If .Cells(i, "B").Value = "" Then
            
                .Cells(i, "D").Formula = "=SUMIF(B1:B" & i - 1 & ",""" & Cells(i - 1, "B").Value & """,D1:D" & i - 1 & ")"
            End If
        Next i
    End With
    
End Sub