Summing a Column with Blanks

[Lvenom]

I have a problem trying to set the references to sum/subtotal a colum with blanks. I have the macro written to find the next blank cell starting at the bottom of my data and working up but I can't get the formula right to sum the numbers above. Here is what my data looks like

Supplier Debit Credit
Vendor A $60 $25
Vendor A $75 $15
Vendor A $55 $35
Sum Blank Blank
Vendor B $60 $40
Vendor B $125 $25
Sum Blank Blank
Vendor C $65 $14

So I want the blanks to be the sum of the numbers above them. I am working from the bottom up so I don't know how to reference the cells to add when they vary (i.e. not always the same number of rows for each vendor. here is my code so far
Sub Macro6()
'
' Macro6 Macro
' Macro recorded 2/27/2007 by User
'

Dim ws As Worksheet
Dim myCol As Long, lastRow As Long
Dim i As Long, firstRow As Long
Dim strHeader As String

Set ws = ActiveSheet

myCol = 3
firstRow = 2
'find last Row in myCol
lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row

For i = lastRow To firstRow Step -1
If Cells(i, myCol + 2) = "" Then
Cells(i, myCol + 2).Select
ActiveCell.FormulaR1C1 = "=SUM($R$C:$R$C)"
End If
Next i

End Sub

Any help would be greatly appreciated

[rylo]

Hi

Try

Please Login or Register  to view this content.

rylo