Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Excel 2002 .. still struggling with VBA for AutoSum!

  1. #1
    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

    Hi All,
    I did post about this awhile ago and I am sorry to say my problem is still not resolved
    A quick recap - I have varying list of values in a column and I wish to add an AutoSum calculation at the bottom whilst executing a macro.
    I currently have the following .. (extract only) :

    Application.CommandBars("Standard").Controls("&Autosum").Execute
    Application.CommandBars("Standard").Controls("&Autosum").Execute
    ActiveCell.Offset(2, 0).Select
    Selection.Copy
    Range("J60").Select

    This code works well in Excel 2000 but 'falls down' in Excel 2002. I have tried a few variations on the AutoSum command but to no avail!
    This is so frustrating! Any ideas?
    Thanks
    S

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What is not working you might have to post all of your code im using 2002
    _________________


    [ This Message was edited by: brettvba on 2002-05-07 15:30 ]

  3. #3
    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

    Hi Brett,
    Thanks for your swift response.
    In essence I produce a Pivot Table and copy a specific 'summary block' to the end of the PT. I am looking to total the PT using the AutoSum function, as I explained previously it works fine in Excel 2000 and 'falls over' in Excel 2002.
    I list below a greater extract of the code. I am not a programmer but I would welcome any other suggestions.

    'Calculate Material Pivot Table
    Sheets("Invoice").Select
    Range("A35").Select
    ActiveSheet.PivotTables("PivotTable2").RefreshTable
    Range("L36:O38").Select
    Selection.Copy
    Range("C36").Select
    Selection.End(xlDown).Select
    'What if no Materials found?
    If ActiveCell.Address = "$C$65536" Then
    Plant_Pivot
    Else
    Mat_Pivot_Cont
    End If

    End Sub


    Sub Mat_Pivot_Cont()
    '
    ' Mat_Pivot_Cont Macro
    'Continuation from If Statement in Material_Pivot Macro

    ActiveCell.Offset(1, -2).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 3).Select
    Application.CutCopyMode = False
    Application.CommandBars("Standard").Controls("&Autosum").Execute
    Application.CommandBars("Standard").Controls("&Autosum").Execute
    ActiveCell.Offset(2, 0).Select
    Selection.Copy
    Range("J60").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Range("A35").Select
    Range("J67").Select

    'Need to move into Plant_Pivot after this!
    Plant_Pivot

    End Sub

    Any other questions, please feel free to ask.
    Cheers
    S

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    doesn't look that good but replace the auto sum command bars with
    AutoSum
    and insert this sub proceedure
    Sub AutoSum()

    Dim cel1, cel2
    ActiveCell.Offset(-1, 0).Select
    cel1 = Selection.End(xlUp).Address
    cel2 = ActiveCell.Address
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = "=sum(" & (cel1) & ":" & (cel2) & ")"

    End Sub

    hope this helps someone might be able to tidy up my code a bit for you

  5. #5
    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

    Hi Brett,
    Just a vote of thanks for your help.
    I have had a opportunity to 'test' it on Excel 2002 at work today and it works fine
    You are a * (read star!)
    Cheers
    S

  6. #6
    Board Regular
    Join Date
    Feb 2011
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2002 .. still struggling with VBA for AutoSum!

    Hello

    I have a similar problem, but I think I need a bit difference in code.

    I have a data, in which I make 3 different tables, for all tables i need auto sum at the end of each table in colum I, J, K, L, M & N. But the problem in my data is that, there may be blanks in the tables in between the rows. Please help me out.

    Quote Originally Posted by brettvba View Post
    doesn't look that good but replace the auto sum command bars with
    AutoSum
    and insert this sub proceedure
    Sub AutoSum()

    Dim cel1, cel2
    ActiveCell.Offset(-1, 0).Select
    cel1 = Selection.End(xlUp).Address
    cel2 = ActiveCell.Address
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = "=sum(" & (cel1) & ":" & (cel2) & ")"

    End Sub

    hope this helps someone might be able to tidy up my code a bit for you

Some videos you may like

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
  •