Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie : How to sum cells via VBA code.
I am using Excel 97.
I want to sum the values of several cells. I tried to follow the examples that I see in this forum and they don't work. My code looks like : lcRange is equal to B3:B35 ActiveCell.Value = "=sum(" & lcRange & ")" I thought that the above command would sum up the values in a column like an auto sum. But it does not work. Can anyone help me ? How can I sum ??? Rich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie : How to sum cells via VBA code.
Hey Rich
Tr ActiveCell.Formula = "=sum(lcRange) You have to tell Excel it's a formula Good luck |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie : How to sum cells via VBA code.
Hi Rich
Is there a special reason of not doing this the 'normal' way A more quicker method if you only want to store a hard-coded value is to do the sum in VBA and then output it to your "activecell" like this ------------------------------------------- Sub Sum( Dim Result As Intege Dim lcRange As Rang Set lcRange = Range("B3:B35" Result = Application.WorksheetFunction.Sum(lcRange ActiveCell.Value = Resul End Su ------------------------------------------- SuperJas. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie : How to sum cells via VBA code.
Rich
works for me: Sub test2() lcRange = "B3:B35" ActiveCell.Value = "=sum(" & lcRange & ")" End Sub Regards Trevor "Rich" wrote in message om... I am using Excel 97. I want to sum the values of several cells. I tried to follow the examples that I see in this forum and they don't work. My code looks like : lcRange is equal to B3:B35 ActiveCell.Value = "=sum(" & lcRange & ")" I thought that the above command would sum up the values in a column like an auto sum. But it does not work. Can anyone help me ? How can I sum ??? Rich |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie : How to sum cells via VBA code.
Rich,
More flexible lcRange = "B3:B" & Cells(Rows.Count,"B").End(xlUp).Row ActiveCell.Formula= "=sum(" & lcRange & ")" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rich" wrote in message om... I am using Excel 97. I want to sum the values of several cells. I tried to follow the examples that I see in this forum and they don't work. My code looks like : lcRange is equal to B3:B35 ActiveCell.Value = "=sum(" & lcRange & ")" I thought that the above command would sum up the values in a column like an auto sum. But it does not work. Can anyone help me ? How can I sum ??? Rich |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie : How to sum cells via VBA code.
There are a lot of ways to do this but here are two
examples: Sub SumRoutine01() ' dimension range of values to sum Dim SumRange As Range ' give the active sheet a name to make this ' more useful in the future Set currentBook = Application.ActiveWorkbook thissheet = ActiveSheet.Name Worksheets(thissheet).Activate Set SumRange = Range(Worksheets(thissheet).Cells(3, 2), Worksheets(thissheet).Cells(35, 2)) Worksheets(thissheet).Cells(1, 1).Value = Application.WorksheetFunction.Sum(SumRange) End Sub Sub SumRoutine02() ' dimension the variables that can be used ' to sum the values. Dim summer, cellvalue As Single ' give the active sheet a name to make this ' more useful in the future Set currentBook = Application.ActiveWorkbook thisSheet = ActiveSheet.Name Worksheets(thisSheet).Activate ' zero the sum variable summer = 0 ' loop to sum the raw values ' I used 3 to 35 to match the row indexes you ' specified. Cells(row,column) For i = 3 To 35 cellvalue = Worksheets(thisSheet).Cells(i, 2).Value summer = summer + cellvalue Next i Worksheets(thisSheet).Cells(1, 1).Value = summer End Sub Hope these help. Raul -----Original Message----- I am using Excel 97. I want to sum the values of several cells. I tried to follow the examples that I see in this forum and they don't work. My code looks like : lcRange is equal to B3:B35 ActiveCell.Value = "=sum(" & lcRange & ")" I thought that the above command would sum up the values in a column like an auto sum. But it does not work. Can anyone help me ? How can I sum ??? Rich . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
[newbie] copy unhidden cells | Excel Discussion (Misc queries) | |||
Newbie Date cells so most recent is on top | Excel Worksheet Functions | |||
VBA Newbie: Help with Do Loop code | Excel Discussion (Misc queries) | |||
Newbie : Help with sort via Excel code | Excel Programming | |||
Newbie : Autofilter thru code ? | Excel Programming |