Excel VBA - Adding Numbers With The SUM Function


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Excel VBA - Adding Numbers With The SUM Function

Hybrid View

  1. #1
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666

    Excel VBA - Adding Numbers With The SUM Function

    I have list of about 150 spreadsheets in this folder that I have to iterate through, open, grab numbers from 20 different places or so and put those numbers on a second spreadsheet (which will comprise a "Summary" of the individual spreadsheets).

    Zipping through each file in the folder and grabbing the numbers was easy. Then, they through a monkey wrench at me.

    In one column on each sheet, there are three rows with different numbers in them. Originally, I was just pulling all three numbers. Now, however, they want me to total these three and bring back just the one total. That is where I'm having a problem. I Googled around and found WorksheetFunction.Sum and figured that would solve my problem. However, I get the error
    Quote Originally Posted by Error
    Run-time error '1004'

    Unable to get the Sum property of the WorksheetFunction Class
    Here is the code (NumFiles is a Function I wrote the counts the number of files in the folder and is not related to this issue)
    Code:
    Dim MarketBasedTemplate As Workbook
    Dim HFRSheet As Workbook
    Dim intRow As Integer
    
    Set MarketBasedTemplate = Workbooks.Open("\\sntXXX\sharename\Market Based\MarketBasedTemplate.xls", UpdateLinks:=0)
    intRow = 4  'start of data feed
    Dim lngOtherTotal As Long
    Label3.Caption = NumFiles("\\sntXXX\sharename\Market Based\Standard_HFR\") & " to process"
    NumStandFiles = NumFiles("\\sntXXX\sharename\Market Based\Standard_HFR\")
    ListBox1.Clear
    strLoad = Dir("\\sntXXX\sharename\Market Based\Standard_HFR\*.xls")
    ChDir "\\sntXXX\sharename\Market Based\Standard_HFR"
    Do While strLoad > vbNullString
       DoEvents
       NumStandFiles = NumStandFiles - 1
       ListBox1.AddItem strLoad
       Label3.Caption = NumStandFiles & " left to process"
       Set HFRSheet = Workbooks.Open("\\sntXXX\sharename\Market Based\Standard_HFR\" & strLoad, UpdateLinks:=0)
       HFRSheet.Sheets("Comments").Select
            HFRSheet.Sheets("Comments").Range("D7").Copy  'provider code
            MarketBasedTemplate.Sheets("Sample Output").Range("A" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
            HFRSheet.Sheets("Comments").Range("D9").Copy  'fye
            MarketBasedTemplate.Sheets("Sample Output").Range("B" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
                    
            HFRSheet.Sheets("HFR Setting").Select  'switch to data sheet
            
            HFRSheet.Sheets("HFR Setting").Range("I21").Copy  'ip operating cost
            MarketBasedTemplate.Sheets("Sample Output").Range("C" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
            HFRSheet.Sheets("HFR Setting").Range("I24").Copy  'ip capital
            MarketBasedTemplate.Sheets("Sample Output").Range("D" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
            HFRSheet.Sheets("HFR Setting").Range("I23").Copy  'ip gme
            MarketBasedTemplate.Sheets("Sample Output").Range("E" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
            HFRSheet.Sheets("HFR Setting").Range("I25").Copy  'ip bad debt
            MarketBasedTemplate.Sheets("Sample Output").Range("F" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
    '---------------- below is what Im trying to get to work                
      lngOtherTotal = Application.WorksheetFunction.Sum("I26:I28") 'total the Other Categories
             MarketBasedTemplate.Sheets("Sample Output").Range("G" & intRow) = lngOtherTotal
    'more code that is similiar to this all the way across to Column W 
    'everything works but above
       HFRSheet.Close
        intRow = intRow + 1
       strLoad = Dir
    Loop
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Quote Originally Posted by Hack
    Code:
    '---------------- below is what Im trying to get to work                
      lngOtherTotal = Application.WorksheetFunction.Sum("I26:I28") 'total the Other Categories
             MarketBasedTemplate.Sheets("Sample Output").Range("G" & intRow) = lngOtherTotal
    The Sum() Function is not part of VBA, it is only part of Excel and can only be used within a cells formula. Try this instead:
    Code:
      With HFRSheet.Sheets("HFR Setting")
        'column "A" is column index 1 so column "I" would be 9
        lngOtherTotal = .Cells(26,9).Value + .Cells(27,9).Value + .Cells(28,9).Value
      End With
      MarketBasedTemplate.Sheets("Sample Output").Range("G" & intRow) = lngOtherTotal

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Ron Weller
    The Sum() Function is not part of VBA, it is only part of Excel and can only be used within a cells formula.
    No wonder I couldn't find a working example of using that function in code.

    Thanks again....I'm sure that will work.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Just tried it...worked like a champ...thanks once again Ron.

    Issue resolved.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Excel VBA add-in portability
    By amotto in forum VB Classic
    Replies: 0
    Last Post: 03-25-2007, 07:05 PM
  2. Getting a GUI to run
    By Eric in forum Java
    Replies: 4
    Last Post: 04-14-2006, 09:09 AM
  3. Packed Data(Comp-3, etc)
    By Marcos in forum VB Classic
    Replies: 3
    Last Post: 01-25-2006, 11:18 AM
  4. Getting a GUI to function
    By Eric in forum Java
    Replies: 1
    Last Post: 11-27-2001, 06:53 AM
  5. sum function
    By gamal in forum VB Classic
    Replies: 1
    Last Post: 06-01-2001, 06:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center