Newbie : How to sum cells via VBA code.

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

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
 
Hey Rich

Tr

ActiveCell.Formula = "=sum(lcRange)

You have to tell Excel it's a formula

Good luck
 
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
 
Rich

works for me:

Sub test2()
lcRange = "B3:B35"
ActiveCell.Value = "=sum(" & lcRange & ")"
End Sub

Regards

Trevor
 
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)
 
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
 
Back
Top