How to write an average formula from VBA- SHOULD BE SIMPLE!

[cantonarv]

I am trying to do the following :

Dim myRange1 as range
Dim myRange2 as range
Dim myAverage as range

'set two ranges
set myRange1= range(b4)
set myRange2= range(e6)

'write into a cell an average formula
myAverage.formula = "=Average(myRange1:myRange2)"

Thats all!!
However the part (myRange1:myRange2) is incorrect - whats the correct
syntax to write this to the cell. Or if this cannot be done how do I
get from numbers to alpha-numeric cell representation e.g. Cell(1,1) to
Cell(A1) -whats the way of doing this is VBA

Thanks in advance guys

 

[Bob Phillips]

myAverage.formula = "=Average(" & myRange1.address & ":" & _
myRange2.address & ")"

 

[Mike Fogleman]

Bob's formula will average ALL cells between B4 & E6 as you indicated with
(myRange1:myRange2).
If you want just the Average of those 2 cells (myRange1, myRange2), then
use:
myAverage.Formula = "=Average(" & myRange1 & "," & myRange2 & ")"

Mike F

 

[davidm]

Two ways:


Sub AverageThem1()

x =WorkSheetFunction.Average(Range("b4"), Range("e6"))
MsgBox x

End Sub

Or to write Average Formula to WorkSheet cell, say Range F2

Sub AverageThem2()

Range("F2").Formula="=Average(b4,e6)"

End Su

 

[Dave Peterson]

One more...

Dim myRange1 as range
Dim myRange2 as range
dim myRange3 as range
Dim myAverage as range

'set two ranges
with activesheet
set myRange1= .range("b4")
set myRange2= .range("e6")
set myrange3= .range(myrange1,myrange2)
set myAverage = .range("a1")
end with


'write into a cell an average formula
myAverage.formula = "=Average(" & myrange3.address(external:=true) & ")"

By using external:=true, you don't have to worry about if myrange3 is on a
different sheet than myaverage.