Doing a Simple Sum in VBA

Hi John,

I just picked up John Walkenbach's book: Excel 2000 Programming for Dummies. Though previously unknown to me, Mr. Walkenbach is apparently nationally renowned.

His book can be of benefit for beginners like you (and me).

To answer your question, you must enter the VBE and manually type the code

VariableName = YourCells

Where VariableName is whatever name you want to hold the total and YourCells can be either single cell references such as:

A1 + B1 + C1

or a range of cells such as:

range("A1:D10")

Get the book, its worth it.

Kevin.

Thanks, but it does not seem to give me a total.

It only picks up the value of the first cell in the
range. I am working on Excel '97...maybe that is the
problem.

There are many ways to do it. For example :-

Sub EnterSumFormula()
Dim startRow As Integer
Dim startCell As range, endCell As range
startRow = 1
With ActiveCell
Set startCell = Cells(startRow, .Column)
Set endCell = .Offset(-1, 0)
.Formula = "=SUM(" & startCell.Address & ":" & endCell.Address & ")"
End With
End Sub

Sub EnterSumValue()
Dim startRow As Integer
Dim startCell As range, endCell As range, rng As range
startRow = 1
With ActiveCell
Set startCell = Cells(startRow, .Column)
Set endCell = .Offset(-1, 0)
Set rng = range(startCell, endCell)
.Value = Application.WorksheetFunction.Sum(rng)
End With
End Sub

Hi John:

Indeed, just as Anon, who has more experience in this than I do, just indicated, there are many ways. Just with the little I've learned in the aforementioned "Dummies" book, here is what I put together in less than 2 minutes and it works.

Sub ShowTotal()
Dim ShowTotal As Integer
ShowTotal = Range("A1") + Range("A2")
Worksheets("Sheet1").Range("A3").Value = ShowTotal
End Sub

Kevin

On a spreadsheet I can manually place the cursor at the

Hi John

There is actually no need to place the cursor, or even select cells in VBA (very rarely). Let's say the numbers you want to Sum are in Column A, then this code would place the Total underneath the last cell in column A.

Sub SumCells()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim rTotalCell As Range
Set rTotalCell = Range("A65536").End(xlUp).Offset(1, 0)
rTotalCell = WorksheetFunction.Sum _
(Range("A1", rTotalCell.Offset(-1, 0)))
Set rTotalCell = Nothing
End Sub

Dave

OzGrid Business Applications

Kevin, thanks for your input. But, I am really looking
for an all-purpose tool. That would sum up any column
of cells on the worksheet as long as the cursor was
placed at the bottom of the column. This is what the
"Auto-sum" icon does when I manually double-click it.

So, I would not be able to call out specific cells like
"A1" + "A2".

It occurs to me that it is very hard to explain these
things in writing.

Hi John: Indeed, just as Anon, who has more experience in this than I do, just indicated, there are many ways. Just with the little I've learned in the aforementioned "Dummies" book, here is what I put together in less than 2 minutes and it works. Sub ShowTotal()

John
If you want this function then this
routine will do it. Just place cursor
as normal @ bottom of column and run the routine.

Sub AutoSum()
CommandBars.FindControl(Id:=226).Execute
Application.SendKeys "~"
End Sub

Ivan Kevin, thanks for your input. But, I am really looking

Ivan, this works great, but...........

Ivan, this works great, but the it works more like
a single click on the AutoSum icon, rather than a
double click. So, the program gets "Hung up"...
I tried using the SendKeys command again, but it
doesn't work...Any thoughts????