Newbie needs your help inserting formulas!

[inigomontoya]

I need a VBA code that looks for an empty row and assigns a formula to certain cells on that row which total all the cells above it up to the previous empty row.

I need to do this for quite a few contiguous cells (columns D-AA).

Here's my code that is not working:

Sub Totals()
Dim LR As Long
Dim i As Long
Dim j As Long
Dim x As Long
x = 4
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To LR
    If Range("A" & i).Value = Empty Then
        For j = 4 To 27 Step 1
            Range(i, j).Formula = "=SUM(i-1, j : x, j)"
        Next j
        x = i
    End If
Next i
End Sub

Also, I would like to format the entire row as well. I need to shade it a certain color.

Please help!

IM

 

[texasalynn]

here is a macro I use for adding totals or sub-totals

Sub AutoSum_Formula()
    Dim x As String
    Dim y As String
    
        x = ActiveCell.Offset(-1, 0).End(xlUp).Address(False, False)
        y = ActiveCell.Offset(-1, 0).Address(False, False)
        
        ActiveCell = "=SUM(" & x & ":" & y & ")"
        
End Sub

Sub AutoSubtotal_Formula()
    Dim x As String
    Dim y As String
    
        x = ActiveCell.Offset(-1, 0).End(xlUp).Address(False, False)
        y = ActiveCell.Offset(-1, 0).Address(False, False)
        
        ActiveCell = "=SUBTOTAL(9," & x & ":" & y & ")"
        
End Sub

 

[inigomontoya]

Thanks! This works well on a cell by cell basis but I forgot to mention that I will have have to run this many times per row and there will be many rows so I'd like to automate as much as possible. Any suggestions?

 

[texasalynn]

can you put this macro as a call in the macro you are using?

 

[inigomontoya]

Good idea... give it a shot.