CODEWITHSUNDEEP
vbaexcel
user2152160
user2152160

Reputation: 1

Summing cells from another sheet using offset

I'm trying to get this code to work for summing cells:

Worksheets("Sheet2").Range("C3").Offset(i, j).Formula = "=Sum("
&Worksheets("Sheet1").Range("A3").Offset(2*i,j).Address & ":" &
Worksheets("Sheet1").Range("A7").Offset(2*i,j).Address & ")"

It keeps giving me the right cells but from the wrong sheet. So for the first iteration I get sum(A3:A7) in cell C3 of Sheet2 but the A3:A7 stays referenced to Sheet2 not Sheet1.

Thanks!

Upvotes: 0

Views: 1416

Answers (2)

Peter Albert
Peter Albert

Reputation: 17515

Try this code - it uses the External:=True parameter of .Addressto retrieve the full address. While this also includes the workbook name, Excel will remove this automatically so you end up with Sheet1!A3. Also note that I used the range A3:A7 as source as .Address can handle multi-cell ranges and you don't need to take care of it manually:

Sheets("Sheet2").Range("C3").Offset(i, j).Formula = "=SUM(" & _
    Sheets("Sheet1").Range("A3:A7").Offset(2 * i, j).Address(External:=True) & ")"

Be aware that hard coding references such as A3 can lead to bugs in the long run, as the user (or even the developer at some stage) might modify the sheet structure. It is best practice to use named ranges, i.e. create a named range for each cell/range you refer to and then access it in VBA with SheetX.Range("rngStartCell") or similar!

Upvotes: 1

Netloh
Netloh

Reputation: 4378

You need to specify the name of the sheet in the formula too. Your code will work if you write it like this:

Worksheets("Sheet2").Range("C3").Offset(i, j).Formula = "=Sum(Sheet1!" & _
Worksheets("Sheet1").Range("A3").Offset(2 * i, j).Address & ":" & _
Worksheets("Sheet1").Range("A7").Offset(2 * i, j).Address & ")"

Upvotes: 2

Related Questions