VBA/Excel/Access/Word/Excel/SUM

Enters a value in 10 cells in a column and then sums the values

 
Sub EnterInfo()
    Dim i As Integer
    Dim cel As Range
    Set cel = ActiveCell
    For i = 1 To 10
        cel(i).Value = 100
    Next i
    cel(i).Value = "=SUM(R[-10]C:R[-1]C)"
End Sub

Reference worksheet and range by name

 
Sub ReferAcrossWorksheets1()
  MsgBox WorksheetFunction.Sum(Sheets("Sheet1").Range( _
      Sheets("Sheet1").Range("A1"), Sheets("Sheet1").Range("A10")))
End Sub

Use with statement with worksheet

 
Sub ReferAcrossWorksheets2()
  With Sheets("Sheet1")
    MsgBox WorksheetFunction.Sum(.Range(.Range("A1"), .Range("A10")))
  End With
End Sub