Sum Range in Excel via VBA in Access

[sandylou]

I want to sum rows within the spreadsheet dyanmically. Here is what I have but what happens is that the first range of cells is the sum that is placed on each of the subsequent ranges. For example I want to sum A1:A15 and then I want to sum A17:A100. And here is the code I have:

Cells(varCurRow, 14).Activate
varFirstCell = ActiveCell.Address
'we need to put the values in the 2nd column
Range((Cells(varCurRow, 2)), (Cells(varCurRow, 2))).CopyFromRecordset rstDetail
'find where we are at in the recordset
varCurRow = varCurRow + varNumRow
'This is the last cell of our range calculation
Cells(varCurRow - 1, 14).Activate
varLastCell = ActiveCell.Address
'this is our calculation range
varAddRange = varFirstCell & ":" & varLastCell
Range(Cells((varCurRow), 14), Cells((varCurRow), 14)).Select
Range(varAddRange).Name = "range1"
ActiveCell.FormulaR1C1 = "=SUM(range1)"