Link to home
Start Free TrialLog in
Avatar of EdLB
EdLB

asked on

Trying to assign formula with dynamic range to a cell

Trying to use the following assignment. Can not use relative referencing, that I know of, because the last column can change.

Dim LastCol as long

'Comment: LastCol = 90

This syntax:
Range("E2").Formula = "= "Average(" & Range (Cells(2,7), Cells(2, LastCol - 2 )) & ")" "
won't compile, says "Syntax error"

This syntax:
Range("E2").Formula = "= ""Average("" & Range (Cells(2,7), Cells(2, LastCol - 2 )) & "")"" "
just copies the text into the cell. The formula does not work.
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You need to use the .Address property of the range object when building your formula:
Range("E2").Formula = "= Average(" & Range (Cells(2,7), Cells(2, LastCol - 2 )).Address & ")"

Note that I deleted two doublequotes as well.
Avatar of EdLB
EdLB

ASKER

Thanks fanpages. That almost works. It generates a working formula in the cell but the location references are absolute references, =AVERAGE($G$2:$CP$2).  I want to copy the formula down a column so need relative addresses. =AVERAGE(G2:CP2)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The Address property of the range lets you specify whether the row and column references are absolute or relative.

Please give the points on this thread to fanpages, because he posted the solution first. I'm just trying to provide the reasons why the changes are needed.
Avatar of EdLB

ASKER

Thanks to both fanpages and byundt for the solution.

byundt I had to give you some points for being first with the complete solution. I have no doubt that fanpages would have arrived at the same solution but speedy response also provides a premium.
Yeah, sorry I have a life outside this site! :)
Avatar of EdLB

ASKER

Dang. What's that like?
At 2:15am (UK time), when I posted the comment, was the best time to ask me that.

I slept (a little) since.