Share via

Using cell references in WorksheetFunction.Sum

Anonymous
2023-01-09T14:19:36+00:00

I am getting an error (Unable to get the Sum property of the WorkSheet function class with this line of code.

ActiveCell.Offset(0, 3).Range("A1").Value = Application.WorksheetFunction.Sum("D" & CStr(FirstRow) & ":D" & CStr(LastRow))

I guess the syntax is incorrect and would appreciate your help in correcting this.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2023-01-09T15:01:31+00:00

ActiveCell.Offset(0, 3).Range("A1").Value = Application.WorksheetFunction.Sum(Range("D" & CStr(FirstRow) & ":D" & CStr(LastRow)))
or easier:

ActiveCell.Offset(0, 3).Value = Application.Sum(Range("D" & FirstRow & ":D" & LastRow))

edit: Ahh, almost simultaneous posting.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2023-01-09T14:55:19+00:00

In VBA, you must supply one or more Range objects as arguments for Application.WorksheetFunction.Sum, not text strings.

ActiveCell.Offset(0, 3).Value = Application.WorksheetFunction.Sum(Range("D" & FirstRow & ":D" & LastRow))

(It isn't necessary to use Range("A1") and CStr here)

Was this answer helpful?

1 person found this answer helpful.
0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest