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.


cantonarv wrote:

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

--

Dave Peterson