CODEWITHSUNDEEP
excelvbareference
Bro2ma
Bro2ma

Reputation: 13

SUM function with data from another sheet

I am trying to use the SUM function in VBA which references another sheet. The function needs to have dynamic inputs thus I am using "Cells".

Simply adding two cells works great with the following code:

   Worksheets("Derived Data").Cells(2, 1).Formula = "=sheet1!" & (Cells(2, 2).Address & "+" & "sheet1!" & Cells(6, 2).Address)

When I try using a function such as SUM or AVERAGE or any other function, VBA interprets the function as a sub macro rather than an EXCEL function

Worksheets("Derived Data").Cells(i, 1).Formula = "=sheet1!" & Sum(Cells(2, 2).Address & ":" & "sheet1!" & Cells(6, 2).Address)

Are my references wrong?

Note: the cells and columns have been fixed for the purpose of debugging.

Upvotes: 0

Views: 4581

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27269

You are almost there, you just don't have the correct syntax to have VBA set the formula. Try this:

Worksheets("Derived Data").Cells(i, 1).Formula = "=Sum(sheet1!" & Cells(2, 2).Address & ":sheet1!" & Cells(6, 2).Address & ")"

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

It was close try this:

Worksheets("Derived Data").Cells(i, 1).Formula = "=sum(sheet1!" & Range(Cells(2, 2), Cells(6, 2)).Address & ")"

Since the formula is asking for a range, using vba you need to use the range.

To make it easier to edit my preference is to split it thus but the single line above works.

Dim rng as Range
with sheets("Sheets1")
    set rng = .Range(.Cells(2, 2), .Cells(6, 2))
end with
Worksheets("Derived Data").Cells(i, 1).Formula = "=sum(" & rng.Parent.Name & "!" & rng.Address & ")"

Upvotes: 1

Related Questions