Error when usign sum function in vba

Question

I have a range of cells (just a simple column) which id like to use the sum function on in vba. The cells are on a separate worksheet from the one I used originally when I created the macro. Its returning a runtime error 1004. Im new to vba so im probably formatting something wrong. Any help would be appreciated!

Worksheets("Sheet2").Cells(16, 3) = WorksheetFunction.Sum(Worksheets("Sheet2").Range(Cells(4, 3), Cells(12, 3)))

score 2 · Accepted Answer · answered Aug 11 '17 at 16:23

Always be sure to qualify Range() and Cells() with the worksheet:

Worksheets("Sheet2").Cells(16, 3) = WorksheetFunction.Sum(Worksheets("Sheet2").Range(Worksheets("Sheet2").Cells(4, 3), Worksheets("Sheet2").Cells(12, 3)))

Or, perhaps a little easier to read:

With Worksheets("Sheet2")
   .Cells(16, 3) = WorksheetFunction.Sum(.Range(.Cells(4, 3), .Cells(12, 3)))
End With

Note when using With, the . is vital! Without that, it's just going to refer to the ActiveSheet's Range() or Cells(), likely causing an error.

Thanks so much! Fixed now. – user392500 Aug 11 '17 at 16:26 — user392500, Aug 11 '17 at 16:26

Error when usign sum function in vba

1 Answers1