VBA for clicking on Sum Button

[jrnyman]

I have a macro in which I need to sum up several rows individually. My problem is that I do not know how many columns will be there ahead of time. Is there a way to use VBA to tell Excel just to click the Sum button in the toolbar?

As a second question on the same topic, is there a way to pass a variable into a SUM formula as in this example:

ActiveCell.FormulaR1C1 = "=SUM(RC[-LastColumn],RC[-1])"
where LastColumn is a variable defined earlier

[Dave Hawley]

Hi jrnyman


You can click the Sum button via VBA using the Excecute Property, but you may find this way a bit better. It will place a Sum at the end a list in Column A : E

Sub SumCols()
Dim i As Integer
For i = 1 To 5
Columns(i).Range("A65536").End(xlUp).Offset(1, 0).Formula _
= "=Sum($A$1:" & Columns(i).Range("A65536").End(xlUp).Address & ")"
Next i
End Sub

[Juan Pablo González]

If you have selected the cell where you want the sum to appear, you can use this macro to "simulate" the clicking on the AutoSum icon. Worked on 2000.

Sub AutoSumClick()
Application.CommandBars.FindControl(ID:=226).Execute
Application.CommandBars.FindControl(ID:=226).Execute
End Sub

That way you don't need to know what row are you in, just select it.

[Sam40mUK]

Juan,
Thank you for posting that code .. most excellent It works a treat in Excel 2000 but falls over in Excel 2002
I have been trying to find out where you got the Button ID number from, without success! Do you happen to know if there is a 'problem' running your posted code in Excel 2002? I was somewhat surprised, I thought it would have been 'upwardly' compatible.
Regards
S

[Mark O'Brien]

How about:

Application.CommandBars("Standard").Controls("&Autosum").Execute  'instead of 226

[Sam40mUK]

Thank you Mark,
I may even rush into work tomorrow to try that code! There again .. Lol
Cheers
S

[Sam40mUK]

After rushing into work this morning .. alas the revised suggestion did not work in Excel 2002 but still works a treat in Excel 2000!
Anybody any other ideas?
S

[Ivan F Moala]

How about trying Daves OR

Sub AutoSum()

CommandBars.FindControl(ID:=226).Execute

Application.SendKeys "~"

End Sub

[Jack in the UK]

the code of Ivans works well this i use..