Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 & ")" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically add a Formula in a worksheet? | Excel Worksheet Functions | |||
Programmatically selecting first worksheet | Excel Programming | |||
Programmatically selecting first worksheet | Excel Programming | |||
How to add an image to a worksheet programmatically | Excel Discussion (Misc queries) | |||
Programmatically Add Worksheet Event | Excel Programming |