Set Range as Variable

[smpatty08]

I am trying to create a macro to enter a formula for the average of all the cells I have selected in the cell immediately below the selected range. I can not seem to find the correct syntax. Here is the code I have thus far.

Sub Test()Dim x As Range, y As Range


Set x = Selection


With Selection.Offset(Selection.Rows.Count).Resize(1, 1)
    .Select
End With


Set y = ActiveCell


y.FormulaR1C1 = "=Average(x)"


With Selection.Font
    .Bold = True
End With


End Sub

Any ideas on how to get this to work? Any help is appreciated!

 

[Fluff]

How about

Sub Test()
   Dim x As Range
   
   Set x = Selection
   
   With x.Offset(x.Rows.Count).Resize(1, 1)
       .FormulaR1C1 = "=Average(" & x.Address(, , xlR1C1) & ")"
       .Font.Bold = True
   End With
End Sub

 

[smpatty08]

Yes! This worked perfectly! Thank You!

 

[My Aswer Is This]

Do you really need a formula in the cell?
Why not have the script enter the results of the formula

Try this:

Sub My_Average()
'Modified  7/20/2018  3:42:55 PM  EDT
Dim ans As Long
ans = Application.WorksheetFunction.Average(Selection)
Selection.Offset(1).Value = ans
End Sub

 

[Fluff]

Yes! This worked perfectly! Thank You!

Glad to help & thanks for the feedback