I am trying to determine a syntax for entering formula
using VBA.
For example if
Dim apple as range
Set apple =("b1:b5")
how to make B6 property averaging apple.
Thanks,
Alex

Please explane more what you want Alex

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"alex" wrote in message ...
I am trying to determine a syntax for entering formula
using VBA.
For example if
Dim apple as range
Set apple =("b1:b5")
how to make B6 property averaging apple.
Thanks,
Alex

Use this Alex

ActiveCell.Formula = "=AVERAGE(apple)"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Alex ekster" wrote in message ...
Thank you very much for your posting.
I am trying to find right syntax to use variable range as an argument in
formula using VBA.
For example how to replace "b1:B5" by a range called "apple" in the
following example:
activecell.Formula= "=AVERAGE("b1:b5")"
Thanks,
Alex



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Is apple a VBA range variable?

dim apple as range
set apple = worksheets("sheet1").range("b1:b5")
activecell.formula = "=average(" & apple.address(0,0) & ")"

is apple a named range:

activecell.formula = "=average(" & range("apple").address(0,0) & ")"

Take a look under help for .address. There's options to include the $ signs
(absolute references) and even include the worksheet name--if the range comes
from a different sheet:

activecell.formula = "=average(" & apple.address(external:=true) & ")"



Alex ekster wrote:

Thank you very much for your posting.
I am trying to find right syntax to use variable range as an argument in
formula using VBA.
For example how to replace "b1:B5" by a range called "apple" in the
following example:
activecell.Formula= "=AVERAGE("b1:b5")"
Thanks,
Alex

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson

I forgot this to add Alex.
For my example use this to name the cells

Worksheets("sheet1").Range("b1:b5").Name = "apple"
ActiveCell.Formula = "=AVERAGE(apple)"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Use this Alex

ActiveCell.Formula = "=AVERAGE(apple)"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Alex ekster" wrote in message ...
Thank you very much for your posting.
I am trying to find right syntax to use variable range as an argument in
formula using VBA.
For example how to replace "b1:B5" by a range called "apple" in the
following example:
activecell.Formula= "=AVERAGE("b1:b5")"
Thanks,
Alex



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!