Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotalling a column of data?
Hello all,
I am going from Ms Access to Ms Excel. Using VBA I create the spreadsheet and create a header section. Next I dump the data into the sheet using Copyrecordset. My question is how can I do a subtotal of a column starting a row 3 until the last cell containing data. Also, the subtotal cell needs to 1 below the last cell containng data. Thnx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotalling a column of data?
Dim rng as Range
Dim icol as 5 ' column E as an example. icol = 5 set rng = Range(cells(3,icol),Cells(rows.count,icol).End(xlu p)) rng(1).offset(rng.rows.count) = "=Sum(" & rng.Address & ")" Change the 1 to the column where you want to do the subtotal. -- Regards, Tom Oglivy "Deville" wrote in message ... Hello all, I am going from Ms Access to Ms Excel. Using VBA I create the spreadsheet and create a header section. Next I dump the data into the sheet using Copyrecordset. My question is how can I do a subtotal of a column starting a row 3 until the last cell containing data. Also, the subtotal cell needs to 1 below the last cell containng data. Thnx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotalling a column of data?
The 1st time I tried this way it worked. But now I get an error almost every other time I run this. Run-time error '1004': Method '~' of object '~' failed Would anyone happen to know how to code this in Ms Access VBA referencing the Excel Object. -----Original Message----- Dim rng as Range Dim icol as 5 ' column E as an example. icol = 5 set rng = Range(cells(3,icol),Cells(rows.count,icol).End (xlup)) rng(1).offset(rng.rows.count) = "=Sum(" & rng.Address & ")" Change the 1 to the column where you want to do the subtotal. -- Regards, Tom Oglivy "Deville" wrote in message ... Hello all, I am going from Ms Access to Ms Excel. Using VBA I create the spreadsheet and create a header section. Next I dump the data into the sheet using Copyrecordset. My question is how can I do a subtotal of a column starting a row 3 until the last cell containing data. Also, the subtotal cell needs to 1 below the last cell containng data. Thnx . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotalling a column of data?
Look in the task manager and close any running instances of Excel. Close
Access and re open. Good chance that you have unreleased instances of Excel. Dim rng as Excel.Range Dim icol as long Dim oxlSheet as Excel.Worksheet Dim oxlApp as Excel.Application set oxlApp = application.GetObject( , "Excel.Application") icol = 5 set oxlSheet = oxlApp.Workbooks("Book1.xls").worksheets(1) set rng = oxlSheet.Range(oxlSheet.cells(3,icol), _ oxlSheet.Cells(rows.count,icol).End(xlup)) rng(1).offset(rng.rows.count) = "=Sum(" & _ rng.Address & ")" Adjust to fit with your current code. -- Regards, TomOgilvy "Deville" wrote in message ... The 1st time I tried this way it worked. But now I get an error almost every other time I run this. Run-time error '1004': Method '~' of object '~' failed Would anyone happen to know how to code this in Ms Access VBA referencing the Excel Object. -----Original Message----- Dim rng as Range Dim icol as 5 ' column E as an example. icol = 5 set rng = Range(cells(3,icol),Cells(rows.count,icol).End (xlup)) rng(1).offset(rng.rows.count) = "=Sum(" & rng.Address & ")" Change the 1 to the column where you want to do the subtotal. -- Regards, Tom Oglivy "Deville" wrote in message ... Hello all, I am going from Ms Access to Ms Excel. Using VBA I create the spreadsheet and create a header section. Next I dump the data into the sheet using Copyrecordset. My question is how can I do a subtotal of a column starting a row 3 until the last cell containing data. Also, the subtotal cell needs to 1 below the last cell containng data. Thnx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotalling Filtered Data Across Sheets | Excel Discussion (Misc queries) | |||
Subtotalling | New Users to Excel | |||
Subtotalling | Excel Worksheet Functions | |||
data subtotalling isnt working properly on my spreadsheet | Excel Worksheet Functions | |||
subtotalling using vba | Excel Programming |