Your problem is not the use of the Value property, VBA stupidly allows you to inject a formula with that, but the fact that you are trying to use VBA objects as part of the formula, and the redundant Range(Cells(... You need to construct the formula with these objects, not just embed them
Code:
For cellcount = 2 To 19 Worksheets("Totals").Cells(9, cellcount).Value = _
"=SUM(" & Range(Cells(11, cellcount), Cells(22, cellcount)).Address(False, False) & ")"
Next cellcount
But you should use Formula anyway
Code:
For cellcount = 2 To 19
Worksheets("Totals").Cells(9, cellcount).Formula = _
"=SUM(" & Range(Cells(11, cellcount), Cells(22, cellcount)).Address(False, False) & ")"
Next cellcount
or FormulaR1C1
Code:
For cellcount = 2 To 19
Worksheets("Totals").Cells(9, cellcount).FormulaR1C1 = _
"=SUM(R11C" & cellcount & ":R22C" & cellcount & ")"
Next cellcount
As all of the formulae are the same relatively, you don't even need a loop
Code:
Worksheets("Totals").Range("B9:S9").Formula = "=SUM(B11:B22)"
Bookmarks