Find a blank line and put in a formula
 

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 ?

Find a blank line and put in a formula
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sverre" wrote in message
...
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 ?

Find a blank line and put in a formula
 

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 ?
Thanks very musch. Im going to try it now

Find a blank line and put in a formula
 

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Sverre" wrote:

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 ?

Find a blank line and put in a formula
 

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


ryguy7272 skrev:

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Sverre" wrote:

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 ?

Find a blank line and put in a formula
 

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---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Sverre" wrote:

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


ryguy7272 skrev:

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Sverre" wrote:

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 ?

Find a blank line and put in a formula
 

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:

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


ryguy7272 skrev:

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Sverre" wrote:

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 ?