VBA for clicking on Sum Button
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: VBA for clicking on Sum Button

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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




  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about:


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


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about trying Daves OR


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


    Kind Regards,
    Ivan F Moala From the City of Sails

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    the code of Ivans works well this i use..


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com