Excel VBA: cannot Sum() over Range()

Question

How should I fix this code because it throws: "The object doesn't support this property or method"

Sub macro1()
    Workbooks("OUTPUT.xls").Sheets("Sheet1").Activate
    ActiveSheet.Range("B4") = _ 
    Workbooks("INPUT.xlsx").Sheets("Sheet1").Sum(Range("D40:D50"))
End Sub

Above code works fine when adjusted as:

Sub macro2()
    Workbooks("OUTPUT.xls").Sheets("Sheet1").Activate
    ActiveSheet.Range("B4") = _
    Workbooks("INPUT.xlsx").Sheets("Sheet1").Range("D40")
End Sub

However it's not acceptable solution because I want to Sum() over Range() as described in macro1().

Arnoud Kooi · Accepted Answer · 2011-04-29 08:57:25Z

5

try

ActiveSheet.Range("B4").Formula = Application.WorksheetFunction.Sum(Range("D40:D50"))

answered Apr 29, 2011 at 8:57

Arnoud Kooi

1,7675 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anders Lindahl · Accepted Answer · 2011-04-29 09:06:18Z

1

WorkSheet doesn't have a sum function, try using WorksheetFunction instead:

Sub macro1() 
    Workbooks("OUTPUT.xls").Sheets("Sheet1").Activate
    ActiveSheet.Range("B4") = _
        Application.WorksheetFunction.Sum(Workbooks("INPUT.xlsx").Sheets("Sheet1").Range("D40:D50"))
End Sub

edited Apr 29, 2011 at 9:06

answered Apr 29, 2011 at 8:59

Anders Lindahl

43.3k9 gold badges93 silver badges94 bronze badges

Collectives™ on Stack Overflow

Excel VBA: cannot Sum() over Range()

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related