Find last used row and insert total

[Delindan]

I have created a spreadsheet by exporting from Access to Excel and now have been trying to clean it up by inserting totals at the bottom of the exported columns. I'm trying to find out 2 things: how to find the last row of data and how to insert the excel =sum() function. Can anyone help?

Thanks!

 

[SkipVought]

hi,

Why would you put your users thru the "try to find the totals" drill, when you could put your totals AT THE TOP, right up there with Bob Uecker, above your table. Give your users a break and take advantage of Excel's aggregation funtions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

I'm trying to recreate something that they've been using. The spreadsheet contains a list of crops and their performances across. The number of crops can vary from report to report so not something I can program into the template. Sorry for being dense but who is Bob Uecker??? (I'm assuming that was a joke!) but more importantly what is an aggregation function?

 

[SkipVought]

Google Bob Uecker.

I'm suggesting that your =SUM(), aggregation function, can be placed in row 1 (in the front row), and the value of the last row of the table can be ignored, using the sheet row count.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

I just realized I was making things a bit too difficult for myself (wouldn't be the first time) I already knew where I was in the spreadsheet and don't need to define any ranges. However, I'm still getting an error 1004 on the sum statement. I'm sure I have the syntax incorrect. I recorded a macro and looked at the syntax that way but I still have something wrong. I'm going to post the code...the sum statement is near the bottom. Thanks!

Option Compare Database
Option Explicit

Private Sub cmdstartreport_Click()

Dim DB As Database
Dim xlApp As New Excel.Application
Dim RSSpecialist As Recordset
Dim strFolder As String
Dim WB As Workbook
Dim strRptTemplate
Dim strDataYear As String
Dim strFileName As String
Dim strSheetName As String
Dim strBreeder As String
Dim strCrop As String
Dim introw As Long
Dim intcol As Long
Dim strSpecialistName As String
Dim intLastRow As Integer



strFolder = Trim(txtFolder)
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
strRptTemplate = strFolder & "FS Breeder Report Template.xlsx"
strFileName = strFolder & Trim(txtFileName)
If Right(strFileName, 5) <> ".xlsx" Then
strFileName = strFileName & ".xlsx"
End If

txtCurrProfile = Null
DoEvents

Set DB = CurrentDb
Set RSSpecialist = DB.OpenRecordset("FS Data", dbOpenSnapshot)

With xlApp
.Visible = False
Set WB = .Workbooks.Open(strRptTemplate)
.Workbooks(1).SaveAs (strFileName)
End With

txtCurrProfile = "Creating " & strFileName & "..."
DoEvents

introw = 1
xlApp.Worksheets(1).Cells(introw, 5) = (Year(Now()) - 3)
xlApp.Worksheets(1).Cells(introw, 13) = (Year(Now()) - 2)
xlApp.Worksheets(1).Cells(introw, 21) = (Year(Now()) - 1)
introw = 3
RSSpecialist.MoveFirst
Do Until RSSpecialist.EOF
strBreeder = RSSpecialist("Breeder")
strCrop = RSSpecialist("Crop")
xlApp.Worksheets(1).Cells(introw, 1) = RSSpecialist("Family")
xlApp.Worksheets(1).Cells(introw, 2) = RSSpecialist("Crop")
xlApp.Worksheets(1).Cells(introw, 3) = RSSpecialist("Sub Crop")
xlApp.Worksheets(1).Cells(introw, 4) = RSSpecialist("Breeder")
xlApp.Worksheets(1).Cells(introw, 31) = RSSpecialist("FS Specialist")
Do While RSSpecialist("Breeder") = strBreeder And RSSpecialist("Crop") = strCrop
If RSSpecialist("Data Year") = (Year(Now()) - 3) Then
intcol = 5
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("New Entries")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 4")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 advcd comm")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 entered FS at phase 4")
intcol = intcol + 2
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("moved phs 4 to phs 6")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("entries renewed")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("Data Year")
ElseIf RSSpecialist("Data Year") = (Year(Now()) - 2) Then
intcol = 13
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("New Entries")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 4")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 advcd comm")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 entered FS at phase 4")
intcol = intcol + 2
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("moved phs 4 to phs 6")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("entries renewed")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("Data Year")
ElseIf RSSpecialist("Data Year") = (Year(Now()) - 1) Then
intcol = 21
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("New Entries")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 4")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 advcd comm")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 entered FS at phase 3")
intcol = intcol + 2
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 entered FS at phase 4")
intcol = intcol + 2
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("moved phs 4 to phs 6")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("entries renewed")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("Estimate of new entries")
End If
RSSpecialist.MoveNext
If RSSpecialist.EOF Then Exit Do
Loop
introw = introw + 1
Loop

xlApp.Worksheets(1).Cells((introw + 2), 1) = "Totals:"

With xlApp
Range((introw + 2), 5).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-introw]C:R[-3]C)"
End With



With xlApp
.Workbooks(1).Save
.Workbooks(1).Close
End With

xlApp.Quit
RSSpecialist.Close
DB.Close

Set xlApp = Nothing
Set RSSpecialist = Nothing
Set DB = Nothing

txtCurrProfile = "Done!"
DoEvents

End Sub

 

[SkipVought]

Dumb to bury aggregations, IMNSHO, but anyhow...

   With xlApp.Worksheets(1)
        .Cells((introw + 2), 1).Value = "Totals:"
    
        .Cells((introw + 2), 5).FormulaR1C1 = "=SUM(R[-introw]C:R[-3]C)"
    End With

I cannot vouch for your SUM R1C1 formula, but you can easily verify the syntax on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

Ok...I believe you when you say what I did was dumb but what the heck is an aggregation...and what do you mean by bury? Putting at the bottom of the spreadsheet? Sorry...just learning this stuff and want to understand what you're saying. Thanks!

 

[Delindan]

I tried what you suggested and am getting an error 424 object required. It's erring on .Cells((introw + 2), 5).FormulaR1C1 = "=SUM(R[-introw]C:R[-3]C)". I copied the syntax from the macro but was wondering if it didn't like the introw for some reason.

 

[Delindan]

I tried replacing the introw in the formula part ("=SUM(R[-introw]C:R[-3]C)" with a real number just to see if it would go through and it did. So apparently it doesn't like introw.

 

[Delindan]

ok...got this to work using the following: xlApp.Worksheets(1).Cells(introw + 2, 5) = WorksheetFunction.Sum(Range(Cells(introw, 5), Cells(3, 5))) I'd still like to understand what you were saying about burying the aggregation! Thanks for the input

 

[SkipVought]

Let's say that you're the user. You just want to see the SUM. It happens to be on row 25,784. You have to scroll DOWN or END + DOWN ARROW, in order to DISCOVER that where the value has benn BURIED. HOW INCONSIDERATE of the worksheet designer, when the SUM (one kind of aggregation, check Mr Webster) could have been placed in a prominent location, like ROW 1, right above the table of on a summary sheet, if there happend to be many aggregations required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

OK I see your point. In this particular case, I've been asked to recreate something they've already been using. Thanks for explaining what you meant and for your help.

 

[SkipVought]

The sum you ended up with is not a formula; rather a value.

Is that what you want?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

Maybe you could explain the difference between WorksheetFunction.Sum(Range(Cells(introw, 5), Cells(3, 5))) and .Cells((introw + 2), 5).FormulaR1C1 = "=SUM(R[-introw]C:R[-3]C)". In my effort to find something that worked, this is what I found and it's true...do not know how it works differently.

 

[SkipVought]

Execute both and observe the difference on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

I see what you mean sooo I'm back at my original problem. When using .Cells((introw + 2), 5).FormulaR1C1 = "=SUM(R[-introw]C:R[-3]C)" it errs on the introw in the sum formula. It recognizes the introw in the cell select just fine. When I substitute a hard number for introw to test the formula part it works just fine so I believe I have that syntax correct. Do you have any thoughts on that?

 

[SkipVought]

Enter the SUM formula manually in the sheet with the proper range. Observe the FORMULA in the formula bar, using the R1C1 reference notation. If that does not help you, then post back with the formula from the Formula Bar COPIED & PASTED here. I NEVER use R1C1 notation, so I cannot venture a solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

Here is what is recorded in the macro ActiveCell.FormulaR1C1 = "=SUM(R[-96]C:R[-3]C)" If I substitute a real number in the formula in place of my variable introw, it works. It's when I put introw in that it has a problem. It seems like when you do commands that are excel commands, it doesn't like the variables. How do you normally do summations like that?

 

[SkipVought]

FormulaR1C1 = "=SUM(R[-" & introw + 2 & "]C:R[-3]C)"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

Thanks...I tried this and got a circular formula warning. When I looked at the formula inserted into the spreadsheet it was =SUM(E96:E1048576). The line in the code for this is .Cells((introw + 2), 5).FormulaR1C1 = "=SUM(R[-" & introw + 2 & "]C:R[-3]C)" Again, when I put real numbers it works fine. You mentioned you would approach this differently. I'm totally open to suggestions