code help to add formula to empty row

[redspanna]

Hi all

I would like some help with code that will look down column A and find first blank cell, once found place a simple formula through columns K:AC of that coresponding row. The formula would SUM all the cells above until blank row is found IN COLUMN I

I have this code that (I think) will find first blank row..

Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("A4:A" & LastRow)
        If rng = "" Then

but not sure if correct and/or how to complete the code to add the formula required

thanks in advance

 

[Andrew Poulsom]

Do all the columns have a blank cell above in the same row?

 

[redspanna]

Once a blank row is found, lets say row 2517, that whole row is blank so I want a formula to SUM the cells above for each column K:AC (ie from row 2516 in this example so =SUM(K2516 upwards to the row containing blank cell in column I, so maybe =SUMKL2516:K2400 because cell I2400 is blank. This formula would then populate all cells in the row K:AC

Does that make sense?

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LastRow As Long
    Dim FirstRow As Long
    With ActiveSheet
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        FirstRow = .Range("I" & LastRow).End(xlUp).Row
        .Range("K" & LastRow + 1 & ":AC" & LastRow + 1).Formula = "=SUM(K" & FirstRow & ":K" & LastRow & ")"
    End With
End Sub