Programmatically add worksheet function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Office_Novice - 21 May 2008 13:58 GMT

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.

Reply to this Message

Chip Pearson - 21 May 2008 14:11 GMT

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.

Signature

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

>I haven't tried this before now and i am having a little trouble.
>
[quoted text clipped - 7 lines]
> But i tried that and the function didn’t recognize my variable. Any help
> would be great.

Reply to this Message

Office_Novice - 21 May 2008 17:01 GMT

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

> 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
[quoted text clipped - 16 lines]
> > But i tried that and the function didn’t recognize my variable. Any help
> > would be great.

Reply to this Message

JW - 21 May 2008 14:13 GMT

On May 21, 8:58 am, Office_Novice
<OfficeNov...@discussions.microsoft.com> wrote:

> I haven't tried this before now and i am having a little trouble.
>
[quoted text clipped - 7 lines]
> 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 & ")"

Reply to this Message

Sam Wilson - 21 May 2008 14:18 GMT

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

> I haven't tried this before now and i am having a little trouble.
>
[quoted text clipped - 7 lines]
> But i tried that and the function didn’t recognize my variable. Any help
> would be great.

Reply to this Message

Bob Phillips - 21 May 2008 14:23 GMT

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

Signature

---
HTH

Bob

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

>I haven't tried this before now and i am having a little trouble.
>
[quoted text clipped - 7 lines]
> But i tried that and the function didn't recognize my variable. Any help
> would be great.

Reply to this Message