Find a blank line and put in a formula

  • Thread starter Thread starter Sverre
  • Start date Start date
S

Sverre

Is it posible automaticly to fond a blank line an put in a formula to
calculate the average values above up to the next blank row ?
 
Change your column mc="f" to yours
This looks for the last cell in mc and then finds the 1st blank above and
then finds the next blank and places the average value in the bottom blank
cell. This case is 2.5 below the 2
6
1

3
2

1

Sub averageaboveblank()
mc = "f"
lr = Cells(Rows.Count, mc).End(xlUp).Row
MsgBox lr
For i = lr To 2 Step -1
If Cells(i, mc) = "" Then
br = i
Exit For
End If
Next i
MsgBox br
nextup = Cells(br - 1, mc).End(xlUp).Row
MsgBox nextup
Cells(br, mc) = _
Application.Average(Range(Cells(nextup, mc), Cells(br, mc)))
End Sub
 
Sverre skrev:
Is it posible automaticly to fond a blank line an put in a formula to
calculate the average values above up to the next blank row ?
Click to expand...
Thanks very musch. Im going to try it now
 
This will calculate multiple averages, at each blank. If you just have one,
that will work too:
Sub PutInAverage()
Dim myA As Range
For Each myA In Selection.SpecialCells(xlCellTypeConstants, 23).Areas
myA.Cells(myA.Rows.count + 1, 1).Resize(1, myA.Columns.count).Formula = _
"=Average(" & myA.Columns(1).Address(False, False) & ")"
Next myA
End Sub

HTH,
Ryan---
 
Thank you, this was perfect, but if i want to start the calculation from
column. G ?


ryguy7272 skrev:
 
Good question. That last amcro looked for a used range, so that may not work
as well for you, as the one below, if you have a specific column that you
want to find the avewrage for. Try this:

Sub subaveragetest()
'place a subtotal in column(V) wherever column(U) has a blank cell
Dim lRow As Long
Dim cell As Range
Dim RowCount As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

lRow = Range("G65536").End(xlUp).Row + 1
RowCount = 0
For Each cell In Range("G2:G" & lRow)
If IsEmpty(cell) Then
cell.Offset(0, 1).FormulaR1C1 = "=Average(R[" & -RowCount &
"]C[-1]:R[-1]C[-1])"
RowCount = 0
Else

The logic should kind of make sense, right. Look at the Gs in the code. I
think you can figure it out!! ;)

HTH,
Ryan---
 
Something is wrong in the frase:
cell.Offset(0,1).FormulaR1C1 ="=Average(R[" & -RowCount&"]C[-1]:R[-1]C[-1])"
Cant figuer out what is wrong.
I also want to calculate the average from column H to column AB


Sverre skrev:
 
Back
Top