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.
EdLBAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
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 & ")"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
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.
EdLBAuthor 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)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
byundtMechanical EngineerCommented:
Range("E2").Formula = "= Average(" & Range (Cells(2,7), Cells(2, LastCol - 2 )).Address(false,false) & ")"
byundtMechanical EngineerCommented:
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.
EdLBAuthor 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 ]IT Services ConsultantCommented:
Yeah, sorry I have a life outside this site! :)
EdLBAuthor Commented:
Dang. What's that like?
[ fanpages ]IT Services ConsultantCommented:
At 2:15am (UK time), when I posted the comment, was the best time to ask me that.

I slept (a little) since.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.