entering formula using VBA

  • Thread starter Thread starter alex
  • Start date Start date
A

alex

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
 
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) & ")"
 
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)"
 
Back
Top