Add function at bottom of Spreadsheet

[Guest]

I have a report that I download every day that varies in length each day. i
want to add in column A the text "Amount" followed by "Item count" on the
next row. Then in column B I want the dollar amount in the same row as
Amount and the Count next to Item Count. I know the functions for the
calculatoin but since the row count varies each day, how can I add this at
the end of each report with a blank row between the end of the report and
this new data.

thank so much,

 

[Nigel]

A code that does it for you to use / adapt.......


Sub AddTotals()
Dim xLastrow As Long
xLastrow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(xLastrow + 1, 1) = "Count"
Cells(xLastrow + 1, 2) = "Amount"
Cells(xLastrow + 2, 1).Formula = "=count(A1:A" & xLastrow & ")"
Cells(xLastrow + 2, 2).Formula = "=sum(A1:A" & xLastrow & ")"

End Sub

 

[Guest]

HI Nigel,

When I run this, it adds the text "Count" and "Amount" to Row 1, columns A &
B respectively. Why would that be if we are designating last row? Also on a
side note, I have lots of code in my macro, at what point is it just too
much? Any help you can give me is so much appreciated. Thanks

Joyce

 

[Guest]

The code as supplied was designed to run against the active sheet. If this
is not the case then .... change it to the following (note you must name the
sheet to your reference and there are decimal points (.) to add to the range
references see code below)

Sub AddTotals()
Dim xLastrow As Long

With sheets("Sheet1") ' << change to sheet name as required

xLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

.Cells(xLastrow + 1, 1) = "Count"
.Cells(xLastrow + 1, 2) = "Amount"
.Cells(xLastrow + 2, 1).Formula = "=count(A1:A" & xLastrow & ")"
.Cells(xLastrow + 2, 2).Formula = "=sum(A1:A" & xLastrow & ")"

End With

End Sub

Cheers
Nigel

 

[Guest]

HI Again Nigel,

I am running it in the current sheet. I open the current sheet and run a
macro that sets up the page for printing...I copied and pasted the code into
the existing macro rso that this was the last action that was being
performed. Could it possibly be just in the wrong place of the macro; I
thought the end would be perfect... the last function being performed. I was
looking at the code, does this add the text "Count" and "Amount" to column A
and the calculation in Column B? That is what I was trying to accomplish,
perhaps I didn't make that clear. I appreciate your help, thanks so much

 

[Nigel]

From what you said you need to make slight changes as follows........

Sub AddTotals()

Dim xLastrow As Long

With ActiveSheet

xLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

.Cells(xLastrow + 1, 1) = "Count"
.Cells(xLastrow + 2, 1) = "Amount"
.Cells(xLastrow + 1, 2).Formula = "=count(A1:A" & xLastrow & ")"
.Cells(xLastrow + 2, 2).Formula = "=sum(A1:A" & xLastrow & ")"

End With

End Sub