Get a total when number or rows is not know in advance
 

if the values are in column B

Range("A65000").End(xlUp).Offset(1, 0).Activate
ActiveCell.Value = "Fees Subtotal"
ActiveCell.Offset(0, 1).Formula = "=sum(B2:B" & ActiveCell.Offset(-1).Row & ")"

--


Gary


"BillyRogers" wrote in message
...
I have this code which returns a recordset from Access and pastes the results
in Excel. I need it to totall columns B and C.
The only problem is that the query will return a different number of rows
each month, so I don't know how to code the sum formula.


SQLcmd = "SELECT * FROM [S 06 Safelite Fee Summary]"

rs.Open Source:=SQLcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4. 0; Data Source=G:\Brad and
Mary's DB.mdb" + _
"; User Id=admin; Password="


Column = 1
Row = 1

For Each fld In rs.Fields

Cells(Row, Column).Value = fld.Name
Cells(Row, Column).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid

End With
Selection.Font.Bold = True


Column = Column + 1

Next fld

Cells(2, 1).CopyFromRecordset rs

Cells.Select
Cells.EntireColumn.AutoFit

Range("A65000").End(xlUp).Offset(1, 0).Activate
ActiveCell.Value = "Fees Subtotal"



--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003