Creating an AVERAGE next to the top of a selection

[Jesse Custer]

This is what I have so far and, being used to the .NET environment and
not having its excellent documentation... Well, I can't figure it out.

My function is extremely simple, but not yet complete because it has
the target cell hard-coded. I want the cell to be the in the next
column to the top of the selection. So if the selection is from C17:C33
then the following should be created in cell D17: =AVERAGE(C17:C33)

My function's code so far:

Range("g20") =
Application.WorksheetFunction.Average(Application.Selection)

 

[Andrew Taylor]

Jesse,
This code should do what you want:

Dim rTarget As Range
Set rTarget = Selection.Cells(1, 1).Offset(0, 1)
rTarget.Formula = "=average(" & Selection.Address & ")"

Actually, in the example you give this would create the
formula =AVERAGE($C$17:$C$33)
If you definitely need the relative reference C17:C33 then
replace the last line of code by

rTarget.Formula = "=average(" & Replace(Selection.Address,"$","") & ")"


HTH
Andrew Taylor

 

[Jesse Custer]

Actually, in the example you give

Yeah, I typed that too quickly. What I meant was that the cell "g20"
would have the value of that formula. Which is all I need, really.
Although, having the formula appear there is equally good.

Thanks for the reply.