CODEWITHSUNDEEP
excelvba
reforrer
reforrer

Reputation: 745

Excel VBA: cannot Sum() over Range()

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().

Upvotes: 2

Views: 11867

Answers (2)

Anders Lindahl
Anders Lindahl

Reputation: 42870

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

Upvotes: 1

Arnoud Kooi
Arnoud Kooi

Reputation: 1727

try

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

Upvotes: 5

Related Questions