CODEWITHSUNDEEP
vbaexcel
user3041384
user3041384

Reputation: 75

VBA return sum of range values from different workbook

I am trying to use VBA in an excel sheet (lets call it wbA) to find the sum of the values in a range of cells in another workbook (lets call that wbB). I have tried using a custom made summing function 

Function sumrange(rng As Range)

summ = 0
For Each cell In rng
summ = summ + cell.Value
Next
sumrange = summ

End Function

which i execute in the wbA here

Sub test4()
Dim app As New Excel.Application

Dim book As Excel.Workbook
Set book = app.Workbooks.Add("...\wbB.xlsm")

With book.Worksheets("November2013")

a = sumrange(Range("B5:T9"))

End With
MsgBox a
End Sub

This returns the sum of the set range in wbA instead of wbB

i have also tried the worksheetfunction.sum option in the following formats

l = book.Worksheets("November2013").Application.WorksheetFunction.Sum(Range(B5:T9"))

and 

With book.Worksheets("December2014")
p = Application.WorksheetFunction.Sum(Range(B5:T9"))
End With

but both caluclate and return the sum of the range from wbA instead of from wbB

How do i write the code to find sum of range in wbB Thanks

Upvotes: 2

Views: 28965

Answers (2)

PowerFritz
PowerFritz

Reputation: 31

For those who are still looking for a one-line-solution:

Cells(10, 1) = WorksheetFunction.Sum(Worksheets("data").Range(Worksheets("data").Cells(3, 35), Worksheets("data").Cells(131, 35)))

Just took it from my code, it addresses the range by cells, but you can easily use RANGE("...") instead of the CELLS(,) function).

The example line above writes the sum of worksheet "data" (range("AI3:AI131")) to A10 of current worksheet.

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149277

Your code works for me with the change that I mentioned. This is what I tried

When you specify a range without fully qualifying it, it will always refer to active sheet. And ActiveSheet might not be the sheet that you expect it to be and hence fully qualify your objects.

Sub test4()
    Dim book As Workbook
    Dim a

    Set book = Workbooks.Open("C:\Book1.xlsx")

    a = sumrange(book.Worksheets(1).Range("A1:A4"))

    MsgBox a
End Sub

Function sumrange(rng As Range)
    summ = 0
    For Each cell In rng
    summ = summ + cell.Value
    Next
    sumrange = summ
End Function

I notice that you are using 

Set book = app.Workbooks.Add("...\wbB.xlsm")

Use .Open Like I have done.

EDIT:

@SiddharthRout yes i am running it from the VBA window in excel – user3041384 3 mins ago

In such a case, you don't need to define your excel application. Your code can be re-written as

Sub test4()
    Dim book As Workbook, a

    Set book = Workbooks.Open("...\wbB.xlsm")

    With book.Worksheets("November2013")
        a = sumrange(.Range("B5:T9"))
    End With

    MsgBox a
End Sub

Upvotes: 1

Related Questions