Macro

[Connie Martin]

I recorded the macro below which needs a little editing and I don't know how
to do it. What I want it to do is average the numbers above the cell
selected starting with the first number below the first blank cell above the
cell selected. That sounds complicated! For example, the cell selected is
D107. D102 is a blank cell. There are numbers in 103 to 106. I want the
macro to run so that it will average the numbers in those cells above. If I
place the cursor in D101 and there are numbers in D77 to D100, then I want it
to average those numbers. I've added an additional step in the macro after
that, but that part of the macro is running fine. It's just that when I
recorded the macro I selected D77 to D100 to average it in D101, and now each
time I run the macro it averages the same number of cells as when I recorded
the macro. If no one answers this, I don't blame you because I am probably
not explaining it very well. Here's the macro, if you can make any heads or
tails about it!! I believe it's just the first line in the macro that needs
editing. Connie

Sub AVERAGE()
'
' AVERAGE Macro
' Macro recorded 5/14/2008 by Connie Martin
'

'
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-24]C:R[-1]C)"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/30"
ActiveCell.Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

 

[Connie Martin]

I'm sorry. Please ignore this post. This is too complicated. I will try
something else. Sorry if anyone has spent time on this. Connie

 

[Ken Hudson]

Hi Connie,
You might try the following macro:

Option Explicit
Dim Anchor As Double
Dim Iloop As Double
Dim Rowcount As Double
Sub Compute_Avg()

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Rowcount = Cells(Rows.Count, "D").End(xlUp).Row
Anchor = 1
For Iloop = 2 To Rowcount
If IsEmpty(Cells(Iloop, "D")) Then
Cells(Iloop, "D") = "=Average(D" & Anchor & "" & Iloop - 1 & ")"
Anchor = Iloop + 1
End If
Next Iloop
Cells(Rowcount + 1, "D") = "=Average(D" & Anchor & "" & Iloop - 1 & ")"

'Turn on warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[jatman]

instead of creating a macro, just type the formula into the cell. creating a
macro for something this simple is too intensive for any user, plus if the
cells that you want to average out changes, you will have to change the
macro. the time invested in changing the macro could be better used to just
do the following:

the formulate that you want, if you want to document the average onto the
spreadsheet, is =average(select your cells)
or you can just select your cells, and look at the bottom it will show a
total and average for what you have selected.

jat

 

[Connie Martin]

Thank you for replying to my jumble. However, this spreadsheet is hundreds
of rows long. To stop and put in an average formula at different intervals
all the way down would be tedious. If I could just click on the cell and
click on a macro button, it would've been so much easier. However, I have
changed my spreadsheet. Just the same, I will see what Ken Hudson's macro
looks like. Thank you once again for even taking the time to read my lengthy
dilemma! Connie

instead of creating a macro, just type the formula into the cell. creating a
macro for something this simple is too intensive for any user, plus if the
cells that you want to average out changes, you will have to change the
macro. the time invested in changing the macro could be better used to just
do the following:

the formulate that you want, if you want to document the average onto the
spreadsheet, is =average(select your cells)
or you can just select your cells, and look at the bottom it will show a
total and average for what you have selected.

jat

I recorded the macro below which needs a little editing and I don't know how
to do it. What I want it to do is average the numbers above the cell
selected starting with the first number below the first blank cell above the
cell selected. That sounds complicated! For example, the cell selected is
D107. D102 is a blank cell. There are numbers in 103 to 106. I want the
macro to run so that it will average the numbers in those cells above. If I
place the cursor in D101 and there are numbers in D77 to D100, then I want it
to average those numbers. I've added an additional step in the macro after
that, but that part of the macro is running fine. It's just that when I
recorded the macro I selected D77 to D100 to average it in D101, and now each
time I run the macro it averages the same number of cells as when I recorded
the macro. If no one answers this, I don't blame you because I am probably
not explaining it very well. Here's the macro, if you can make any heads or
tails about it!! I believe it's just the first line in the macro that needs
editing. Connie

Sub AVERAGE()
'
' AVERAGE Macro
' Macro recorded 5/14/2008 by Connie Martin
'

'
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-24]C:R[-1]C)"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/30"
ActiveCell.Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

 

[Connie Martin]

Ken, that macro works fabulously! It throws some zeros in empty cells but
that's easy to rectify. It does the whole sheet in one click on the mouse!
Thank you! You are fabulous. The fact that you even understood my jumble is
something else, and then to come up with the answer!! Wow! I'm impressed!
Thank you so much! Connie

Hi Connie,
You might try the following macro:

Option Explicit
Dim Anchor As Double
Dim Iloop As Double
Dim Rowcount As Double
Sub Compute_Avg()

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Rowcount = Cells(Rows.Count, "D").End(xlUp).Row
Anchor = 1
For Iloop = 2 To Rowcount
If IsEmpty(Cells(Iloop, "D")) Then
Cells(Iloop, "D") = "=Average(D" & Anchor & "" & Iloop - 1 & ")"
Anchor = Iloop + 1
End If
Next Iloop
Cells(Rowcount + 1, "D") = "=Average(D" & Anchor & "" & Iloop - 1 & ")"

'Turn on warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

--
Ken Hudson

I recorded the macro below which needs a little editing and I don't know how
to do it. What I want it to do is average the numbers above the cell
selected starting with the first number below the first blank cell above the
cell selected. That sounds complicated! For example, the cell selected is
D107. D102 is a blank cell. There are numbers in 103 to 106. I want the
macro to run so that it will average the numbers in those cells above. If I
place the cursor in D101 and there are numbers in D77 to D100, then I want it
to average those numbers. I've added an additional step in the macro after
that, but that part of the macro is running fine. It's just that when I
recorded the macro I selected D77 to D100 to average it in D101, and now each
time I run the macro it averages the same number of cells as when I recorded
the macro. If no one answers this, I don't blame you because I am probably
not explaining it very well. Here's the macro, if you can make any heads or
tails about it!! I believe it's just the first line in the macro that needs
editing. Connie

Sub AVERAGE()
'
' AVERAGE Macro
' Macro recorded 5/14/2008 by Connie Martin
'

'
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-24]C:R[-1]C)"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/30"
ActiveCell.Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub