I'm trying to do a sum on a varying number of rows in a column. I have
to do this for a number of worksheets but I'm focusing on getting the
code right for just one of the worksheets right now (the active one).
At the moment the starting row for all data in these worksheets is Row
7.

Example:

Sheet1

row 7 - 2
row 8 - 10
row 9 - 5

Sheet2

row 7 - 8
row 8 - 12
row 9 - 14
row 10 - 20
row 11 - 4
row 12 - 6

The module is something I picked up here:

http://www.beyondtechnology.com/geeks012.shtml
<http://www.beyondtechnology.com/geeks012.shtml>

It looks like this:

Option Explicit

Function LastCell(WS As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With WS

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = WS.Cells(LastRow&, LastCol%)

End Function

The subprocedure that calls this procedure (pardon me if I'm getting my
terminology mixed up - I'm new to using VBA) is:

Sub SumVarRange()
Dim x As Integer
Dim y As Integer
Dim BegRng As Variant
Dim EndRng As Variant
Dim CountRng As Variant

x = LastCell(ActiveSheet).Row
y = LastCell(ActiveSheet).Column
'MsgBox x & " " & y
BegRng = "A7"
EndRng = "A" & x
MsgBox BegRng
MsgBox EndRng
CountRng = ActiveSheet.Range(BegRng & ":" & EndRng).Count
MsgBox CountRng
ActiveSheet.Range(EndRng).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[" - "& CountRng]C:R[-1]C)"
End Sub

My problem is trying to pass the count of rows between BegRng and EndRng
inclusive. I highlighted this in yellow where my passing a variant in
string failed.

1) How can I fix the above to pass the value of CountRng into the Sum
formula

2) Is there a more efficient way handling varying ranges for summing,
formatting etc?

3) Where should I be putting the above subprocedure (at least that's
what I'm calling it) - a specific worksheet object - or - in another
module?

a) I understand the idea of a module and then a sub calling it but I
haven't any idea where the best place is to put the sub procedure.

4) Maybe there's a good book on VBA for Excel that can help me get basic
syntax, terminology and a good understanding of how to work in VBA?

a) I'm looking for one that will get a newbie like me up to speed so
I'll be less timid about experimenting and not get hung up on the little
stuff.