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 ?
calculate the average values above up to the next blank row ?
D
Don Guillett
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
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
S
Sverre
Sverre skrev:
Thanks very musch. Im going to try it now
R
ryguy7272
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---
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---
S
Sverre
Thank you, this was perfect, but if i want to start the calculation from
column. G ?
ryguy7272 skrev:
column. G ?
ryguy7272 skrev:
R
ryguy7272
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---
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---
S
Sverre
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:
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: