findblank cell in column, insert formula then next blank cell and so on

[ronjo]

I've searched to no avail. I have a sheet consisting of 7 columns and a variable number of rows. The rows are split at random intervals with an empty row.
I wish to find each blank cell in turn in column G and insert an average function for the range of cells immediately above the blank cell. The range will contain some cells with text.
Please can anyone help?

 

[ScottD]

Try this:

Sub avg_blank()
lr = Range("g" & Rows.Count).End(xlUp).Row
astart = 5 'change 5 to first row of data
For i = 5 To lr + 1 'change 5 to first row of data
If Range("g" & i).Value = "" Then
aend = i - 1
Formu = "=average(g" & astart & ":g" & aend & ")"
Range("g" & i).Value = Formu
astart = i + 1
End If
Next i
End Sub

 

[ronjo]

What can I say - Thank you very much ScottD it is exactly what I was going round in circles trying to solve and works perfectly. Thanks again and kind regards.