Excel VBA - Adding Numbers With The SUM Function

[Hack]

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

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)

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

[Ron Weller]

'---------------- 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:

  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

[Hack]

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.

[Hack]

Just tried it...worked like a champ...thanks once again Ron.

Issue resolved.