Programmatically add worksheet function

[Office_Novice]

I haven't tried this before now and i am having a little trouble.

This is what i have:
Worksheets("Sheet2").Range("C1").Value = "=SUM(C2:C5000)"

And it works ok, But i wont always know the range. I need something like:

Worksheets("Sheet2").Range("C1").Value = "=SUM(C2:C" & LastRow)"

But i tried that and the function didn’t recognize my variable. Any help
would be great.

 

[Chip Pearson]

You can't use a variable directly within the formula string because that is
passed to Excel and Excel has no idea about VBA variable. Your code is very
close to what you want. Try

Worksheets("Sheet2").Range("C1").Value = "=SUM(C2:C" & LastRow & ")"

Also, as a matter of style, I would change .Value to .Formula. Value will
work, but Formula is more accurate and self-explanatory.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JW]

I haven't tried this before now and i am having a little trouble.

This is what i have:
Worksheets("Sheet2").Range("C1").Value = "=SUM(C2:C5000)"

And it works ok, But i wont always know the range. I need something like:

Worksheets("Sheet2").Range("C1").Value = "=SUM(C2:C" & LastRow)"

But i tried that and the function didn’t recognize my variable. Any help
would be great.

Your inserting a formula. Why are you using .Value? You should use
something like this:
Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Worksheets("Sheet2").Range("C1").Formula = _
"=SUM(C2:C" & LastRow & ")"

 

[Sam Wilson]

Worksheets("Sheet2").Range("C1").formula = "=SUM(C2:" &
range("c2").end(xldown).address(false,false) & ")"

 

[Bob Phillips]

Worksheets("Sheet2").Range("C1").Value = "=SUM(C2:C" & LastRow & ")"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Office_Novice]

Thanks for the help. The .Formula tip will be most useful.