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
|