Page 1 of 2 12 LastLast
Results 1 to 10 of 17

How to use excel sum function using vba

This is a discussion on How to use excel sum function using vba within the Excel Questions forums, part of the Question Forums category; Folks, I've build the following code. It runs thru okay with sort. But gives trouble when get to ".Columns..." line. ...

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    293

    Default How to use excel sum function using vba

    Folks,
    I've build the following code. It runs thru okay with sort. But gives trouble when get to ".Columns..." line. What am I doing wrong here.?
    Sub macro()
    With Range("A2:J5000")
    .Sort key1:=Range("A2"), Order1:=xlAscending, _
    Header:=xlGuess, Ordercustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
    .Columns(1).Find(what:="Non-ILEC", LookAt=xlWhole)
    End With
    End Sub

    Secondly,
    I build this code, it works perfectly. But can someone please have a look and show me how it can be rewritten with less code lines. Basically, I want to insert 3 lines and type total and sum value.
    Sub macro()
    Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).EntireRow.Insert
    Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).EntireRow.Insert
    Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).EntireRow.Insert
    Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).Offset(-2).Value = "Total BS ILEC Orders"
    End Sub

    Thirdly,
    Can I use excel sum function in vba? if not...what is the alternative.?
    How would I total "Total BS ILEC Orders" when the "Amount" column is G.

  2. #2
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default Re: How to use excel sum function using vba

    irfananeeza,

    Two out of three.

    I've build the following code. It runs thru okay with sort. But gives trouble when get to ".Columns..." line. What am I doing wrong here.?
    Code:
    Sub macro1()
        With Range("A2:J5000")
            .Sort key1:=Range("A2"), Order1:=xlAscending, _
            Header:=xlGuess, Ordercustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
            .Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).EntireRow.Insert
        End With
    End Sub

    Secondly,
    I build this code, it works perfectly. But can someone please have a look and show me how it can be rewritten with less code lines. Basically, I want to insert 3 lines and type total and sum value.
    Code:
    Sub macro2()
        Dim FR&
        FR& = Application.WorksheetFunction.Match("Non-ILEC", Range("A:A"), 0)
        Range("A" & FR& & ":A" & FR& + 2).EntireRow.Insert
        Range("A" & FR&).Offset(1).Value = "Total BS ILEC Orders"
    End Sub

    Have a great day,
    Stan

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    13,906

    Default Re: How to use excel sum function using vba

    Third:

    Code:
    WorksheetFunction.Sum(Range("G1:G5000"))
    Or:

    Code:
    Range("A5001").Formula = "=Sum(G1:G5000)"
    Or:
    Code:
    With Range("A5001")
        .Formula = "=Sum(G1:G5000)"
        .value = .value '//hard values
    End With

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default Re: How to use excel sum function using vba

    irfananeeza,

    And, the third part:

    Before the macro:

    Sheet1

     A
    11
    22
    33
    44
    55
    66
    77
    88
    99
    1010
    1111
    1212
    1313
    1414
    1515
    1616
    17Non-ILEC
    1818
    1919
    2020
    21 


    Excel tables to the web >> Excel Jeanie HTML 4


    After the macro:

    Sheet1

     A
    11
    22
    33
    44
    55
    66
    77
    88
    99
    1010
    1111
    1212
    1313
    1414
    1515
    1616
    17136
    18Total BS ILEC Orders
    19 
    20Non-ILEC
    2118
    2219
    2320
    24 

    Spreadsheet Formulas
    CellFormula
    A17=SUM(A1:A16)


    Excel tables to the web >> Excel Jeanie HTML 4


    Code:
    Sub SUMinVBA()
        Dim FR&
        FR& = Application.WorksheetFunction.Match("Non-ILEC", Range("A:A"), 0)
        Range("A" & FR& & ":A" & FR& + 2).EntireRow.Insert
        Range("A" & FR&).Offset(1).Value = "Total BS ILEC Orders"
        With Range("A" & FR&)
            .Formula = "=SUM(A1:A" & FR& - 1 & ")"
            .Font.Bold = True
        End With
    End Sub

    In the above code, change all "A"s to "G"s.


    Have a great day,
    Stan

  5. #5
    Board Regular
    Join Date
    Feb 2008
    Posts
    293

    Default Re: How to use excel sum function using vba

    Stanleydgromjr/Alexander Barnes:

    All I can say i am soooooo thankful to both of you.

    It will take me a day or two to inform you if code runs okay, if I have completely understood its construction.

    Thanks. I am very appreciative of it.

  6. #6
    Board Regular
    Join Date
    Feb 2008
    Posts
    293

    Default Re: How to use excel sum function using vba

    Stanleydgromjr,

    I understood everything very clearly in your code except one little thing.

    I don't understand the use of this specific &" in the code. If I remove it, code does not function.

    Code:
     
    .Formula = "=SUM(A1:A" & FR& - 1 & ")"

    Interpretation of the code: Assume FR& = 6th row
    Sum = (A1: A 5 ) To me it should finish here because the range is complete i.e. A1:A5

    However, looking at your code
    Sum=(A1: A5 & ") This red piece I have trouble understanding it.

  7. #7
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default Re: How to use excel sum function using vba

    irfananeeza,
    Code:
            'the next code line causes        ' Compile error:        '   Expected end of statement        '        ' the format is not correct        .Formula = "=SUM(A1:A" & FR& - 1 )"
    Having a problem putting it into words.If I used:.Formula = "=SUM(A1:A5)"it would work.But, I did not know exactly what the range in the formula would be.I know from experience, that if you are using VBA to put a formula into a cell, you have to create what looks like a string.Sometimes you have to play to get the compiler to be satisfied.Sorry. I just can not explain it better.Have a great day,Stan

  8. #8
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default Re: How to use excel sum function using vba

    irfananeeza,

    Having a problem putting it into words.

    Code:
            'the next code line causes
            ' Compile error:
            '   Expected end of statement
            '
            ' the format is not correct
            .Formula = "=SUM(A1:A" & FR& - 1 )"


    If I used:
    .Formula = "=SUM(A1:A5)"

    it would work.


    But, I did not know exactly what the range in the formula would be.

    I know from experience, that if you are using VBA to put a formula into a cell, you have to create what looks like a string.

    Sometimes you have to play to get the compiler to be satisfied.

    Sorry. I just can not explain it better.


    Have a great day,
    Stan

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    13,906

    Default Re: How to use excel sum function using vba

    I'm not used to seeing the & in a variable name...Is that a convention of some kind?

    Why not simply as below - now all "&" signs are string concatenation operators (is that easier to read?):
    Code:
    Sub SUMinVBA()
        Dim FR as Long
        FR = Application.WorksheetFunction.Match("Non-ILEC", Range("A:A"), 0)
        Range("A" & FR & ":A" & FR + 2).EntireRow.Insert
        Range("A" & FR).Offset(1).Value = "Total BS ILEC Orders"
        With Range("A" & FR)
            .Formula = "=SUM(A1:A" & FR - 1 & ")"
            .Font.Bold = True
        End With
    End Sub

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,664

    Default Re: How to use excel sum function using vba

    I'm not used to seeing the & in a variable name...Is that a convention of some kind?
    It's not part of the variable name, it's a type-declaration character

    From the help file:

    type-declaration character
    A character appended to a variable name indicating the variable's data type. By default, variables are of type Variant unless a corresponding Deftype statement is present in the module.

    & is for type Long
    % is Integer
    $ is String
    @ is Currency
    # is Double
    ! is Single

    It's a VB6 article, but see here:
    http://www.vb6.us/tutorials/using-variables-vb6
    Last edited by Scott Huish; Nov 28th, 2008 at 03:07 PM.
    Office 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

Page 1 of 2 12 LastLast

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