Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to show the last number in a rows in the total pending row | New Users to Excel | |||
advance number by 1 when compared | Excel Discussion (Misc queries) | |||
Is there a way to automatically advance a number when printing? | Excel Worksheet Functions | |||
Add total number of rows (text) in a column | Excel Worksheet Functions | |||
Can I disable this automatic number advance thing? | Excel Discussion (Misc queries) |