Hi all,

Could anyone help me in solving this problem.

I have a dataset in one column (G) containing only numbers. Every nth
row in that column is empty (so it varies). In this empty cell I want
to calculate the average of the n cells above. n is defined by the next
empty row. The data looks like this:
G
3
3
3
empty
3
3
empty
3
3
3
3
3
empty

My question is: Is there a VBA-sollution to insert an average in every
empty cell and where the average is based on the n cells above untill
its hits another empty cell.

Public Sub ProcessData()
Const TEST_COLUMN As String = "G" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim istart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
istart = 1
For i = 2 To iLastRow + 1
If .Cells(i, TEST_COLUMN).Value = "" Then
.Cells(i, TEST_COLUMN).Value =
Application.Average(.Cells(istart, TEST_COLUMN).Resize(i - istart + 1))
istart = i + 1
End If
Next i

End With

End Sub

--
HTH

Bob

(change the xxxx to gmail if mailing direct)
"PP" wrote in message
ups.com...
Hi all,

Could anyone help me in solving this problem.

I have a dataset in one column (G) containing only numbers. Every nth
row in that column is empty (so it varies). In this empty cell I want
to calculate the average of the n cells above. n is defined by the next
empty row. The data looks like this:
G
3
3
3
empty
3
3
empty
3
3
3
3
3
empty

My question is: Is there a VBA-sollution to insert an average in every
empty cell and where the average is based on the n cells above untill
its hits another empty cell.

Dear Bob,

Thanks very much for your reply. It works great. Since I am a newbe on
VBA I knew this was possible, just not on how to program it.

Still, I was wondering if it is possible to modify the code in such a
way that instead of calculating the average underneath the blocks, it
calculates it above the blocks. I've tried to alter some plus and minus
signs, but that didn't work.

Thanks,
Pim

Assuming that there is a blank at the top

Public Sub ProcessData()
Const TEST_COLUMN As String = "G" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
iStart = iLastRow
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value = "" Then
.Cells(i, TEST_COLUMN).Value = Application.Average _
(.Cells(i + 1, TEST_COLUMN).Resize(iStart - i))
iStart = i - 1
End If
Next i

End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"PP" wrote in message
ps.com...
Dear Bob,

Thanks very much for your reply. It works great. Since I am a newbe on
VBA I knew this was possible, just not on how to program it.

Still, I was wondering if it is possible to modify the code in such a
way that instead of calculating the average underneath the blocks, it
calculates it above the blocks. I've tried to alter some plus and minus
signs, but that didn't work.

Thanks,
Pim

Dear Bob,

many thanx!! You safed me a ton load of boring work!

I first had to cut your commentline and paste on a new line underneath
the line. On the Windowsmachine I used this afternoon this wasn't
necessary, but on my Mac this was... .

Again many thanx!
Pim