Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    =sum in variable (2003)

    I am using a VB variable to sum a group of cells, then paste that total elsewhere.
    I just can

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =sum in variable (2003)

    Sum is a worksheet function, not a VBA function. To use it in VBA, prefix it with the WorksheetFunction object, and use a range as argument.

    Junk = Application.WorksheetFunction.Sum(Range(

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =sum in variable (2003)

    Fantastic, thanks Hans, as always.
    Is this the best way to accomplish this? That is, to assign the sum of a range to a variable?
    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =sum in variable (2003)

    It depends on what you want to accomplish.

    If you want cell X5 to update itself automatically, you wouldn't have to use code at all, simply enter the formula =SUM(B5:B10) in X5.

    Or you could use code to place the formula in X5:

    Range("X5").Formula = "=SUM(B5:B10)"

    If you want to place a static result in cell X5, you don't really need a variable as intermediate, you can do it directly:

    Range("X5") = Application.WorksheetFunction.Sum(Range("B5:B10"))

    You can also calculate the sum in a loop, but this is less efficient than using the worksheet function.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =sum in variable (2003)

    Here's a more generic version that I assigned a hot key to. Just select any group of cells, hit the hot key and paste the sum where you will.


    Sub SelectionSumCopy()
    'may need to create and delete a userform in order
    'to force a reference to MS Forms 2.0 Object Lib
    Dim MyData As DataObject
    Set MyData = New DataObject
    MyData.SetText Application.WorksheetFunction.Sum(Selection)
    MyData.PutInClipboard
    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =sum in variable (2003)

    Thanks Hans and others,
    what I wanted to do was to sum a range of cells and apply that to a variable.
    I can get around that by the code Application.WorksheetFunction.Sum(Range("B5:B10")) but I was wondering if there's a more efficient way.
    Thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =sum in variable (2003)

    Using Application.WorksheetFunction.Sum is the most efficient way.

Posting Permissions

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