Trying to assign formula with dynamic range to a cell

EdLB
EdLB used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Commented:
If I am correct in my understanding of what you are trying to achieve, I suggest that you use this statement:

Range("E2").Formula = "=Average(" & Range(Cells(2, 7), Cells(2, LastCol - 2)).Address & ")"
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
EdLB

Author

Commented:
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)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Range("E2").Formula = "= Average(" & Range (Cells(2,7), Cells(2, LastCol - 2 )).Address(false,false) & ")"
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
EdLB

Author

Commented:
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.
fanpages

Commented:
Yeah, sorry I have a life outside this site! :)
EdLB

Author

Commented:
Dang. What's that like?
fanpages

Commented:
At 2:15am (UK time), when I posted the comment, was the best time to ask me that.

I slept (a little) since.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial