Programmatically add worksheet function
 

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 didnt recognize my variable. Any help
would be great.

Programmatically add worksheet function
 

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)




"Office_Novice" wrote in message
...
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 didnt recognize my variable. Any help
would be great.

Programmatically add worksheet function
 

On May 21, 8:58*am, Office_Novice
wrote:
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 & ")"

Programmatically add worksheet function
 

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

"Office_Novice" wrote:

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 didnt recognize my variable. Any help
would be great.

Programmatically add worksheet function
 

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" wrote in message
...
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.

Programmatically add worksheet function
 

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

"Chip Pearson" wrote:

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)




"Office_Novice" wrote in message
...
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 didnt recognize my variable. Any help
would be great.