Get a total when number or rows is not know in advance
Not sure which colum your needing to sum but this may work
With Worksheets("Sheet1")
If IsEmpty(.Cells(.Rows.Count, 2)) Then
With .Cells(.Rows.Count, 2).End(xlUp)
.Offset(2, 0).Formula = "=Sum($B$6:" & _
.Address & ")"
End With
End If
"BillyRogers" wrote:
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
|