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
"Bob Phillips" wrote in message
...
myAverage.formula = "=Average(" & myRange1.address & ":" & _
myRange2.address & ")"

--
HTH

Bob Phillips

"cantonarv" wrote in message
oups.com...


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