macro averages number above and below
You could do it manually, too--well, if those cells that will contain the
averages are empty.
Select the range to inspect
Edit|goto|Special|blanks
Notice that only the cells getting the =average() formula are selected.
type this:
=average(
hit the uparrow, then the comma, then the down arrow.
Then hit the close parent )
Now hit ctrl-enter to fill the selection with that formula. Excel will adjust
the formula for each of the cells.
In code, it would look like:
Option Explicit
Sub testme()
Dim myRng As Range
Dim myBlanks As Range
With ActiveSheet
Set myRng = .Range("a1:h20") 'whatever???
Set myBlanks = Nothing
On Error Resume Next
Set myBlanks = myRng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If myBlanks Is Nothing Then
MsgBox "no blanks!"
Else
myBlanks.FormulaR1C1 = "=AVERAGE(R[-1]C,R[1]C)"
End If
End With
End Sub
J-Chef-T wrote:
Needing to set a macro that can create averages of the number directly above
and the number directly below. For example the spreadsheet would looke like
so.
2
avg of 2&3
3
4
avg of 4&5
help?
--
Dave Peterson
|