How about this,

Dim dblSum As Double
' Sum column  A of Sheet1 in thisworkbook
dblSum =
Application.WorksheetFunction.Sum(ThisWorkbook.Worksheets("Sheet1").Columns(1))

I think the Mismatch error  is caused by a cell containing text instead
of a number.

Also your original  code loops across the columns in row 1
.cells(1, i)
whereas the question  suggests you want to move down the rows in a column.