Add totals at end of the report with unknow number of rows with VB

[SITCFanTN]

I have a report that I download into Excel each day. The amount of rows vary
each day. I want to add to an existing macro code that will add at the end
of the report (regardless of how many rows there are), in column the text of
"Total" and then add the sum of column B (currency) and place that total in
column B on the same row as Total. On the next row I would like to add the
text "Items" in column A and then have the count display in Column B. I just
don't know the code to show this because of not being able to designate what
row it will display on. I could have 1500 rows of data or 3000 rows of data.
I would like to skip one row and then add the "Total" text and calculation
and below that at the Items count. I appreciate any help you can give me.
Thanks so much

[Paul B]

SAITCFanTN, try this,

Sub addthem()
Dim rng As Range
Set rng = Cells(Rows.Count, 2).End(xlUp)

rng.Offset(2, -1).Value = "Total"
rng.Offset(3, -1).Value = "Items"

rng.Offset(2, 0).Value = Application.Sum(Range("B1", rng))
rng.Offset(3, 0).Value = Application.Count(Range("B1", rng))

End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"SITCFanTN" <[email protected]> wrote in message
news:[email protected]...
> I have a report that I download into Excel each day. The amount of rows
vary
> each day. I want to add to an existing macro code that will add at the
end
> of the report (regardless of how many rows there are), in column the text
of
> "Total" and then add the sum of column B (currency) and place that total
in
> column B on the same row as Total. On the next row I would like to add
the
> text "Items" in column A and then have the count display in Column B. I
just
> don't know the code to show this because of not being able to designate
what
> row it will display on. I could have 1500 rows of data or 3000 rows of
data.
> I would like to skip one row and then add the "Total" text and
calculation
> and below that at the Items count. I appreciate any help you can give me.
> Thanks so much

[JOUIOUI]

Hi Paul, this worked great! Thank you so much. I have one more question,
since my "Count" calculation is going into a currency formated column, how
can I have that one cell be bolded text. Its showing as currency now.

"Paul B" wrote:

> SAITCFanTN, try this,
>
> Sub addthem()
> Dim rng As Range
> Set rng = Cells(Rows.Count, 2).End(xlUp)
>
> rng.Offset(2, -1).Value = "Total"
> rng.Offset(3, -1).Value = "Items"
>
> rng.Offset(2, 0).Value = Application.Sum(Range("B1", rng))
> rng.Offset(3, 0).Value = Application.Count(Range("B1", rng))
>
> End Sub
>
>
> --
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
> "SITCFanTN" <[email protected]> wrote in message
> news:[email protected]...
> > I have a report that I download into Excel each day. The amount of rows
> vary
> > each day. I want to add to an existing macro code that will add at the
> end
> > of the report (regardless of how many rows there are), in column the text
> of
> > "Total" and then add the sum of column B (currency) and place that total
> in
> > column B on the same row as Total. On the next row I would like to add
> the
> > text "Items" in column A and then have the count display in Column B. I
> just
> > don't know the code to show this because of not being able to designate
> what
> > row it will display on. I could have 1500 rows of data or 3000 rows of
> data.
> > I would like to skip one row and then add the "Total" text and
> calculation
> > and below that at the Items count. I appreciate any help you can give me.
> > Thanks so much
>
>
>

[Paul B]

How about changing the format for the count cell, try this, tested with 2002
and when I put in new data the cell changes back to currency, test it and
see if this works for you, if not may need to also format the cell back to
currency before the macro gets new data


Sub addthem()
Dim rng As Range
Set rng = Cells(Rows.Count, 2).End(xlUp)

rng.Offset(2, -1).Value = "Total"
rng.Offset(3, -1).Value = "Items"

rng.Offset(2, 0).Value = Application.Sum(Range("B1", rng))
rng.Offset(3, 0).Value = Application.Count(Range("B1", rng))
rng.Offset(3, 0).NumberFormat = "General"

'uncomment if you want it bold, but when I tried it the cell did not
'format back to currency the next time and stayed bold
'rng.Offset(3, 0).Font.Bold = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"JOUIOUI" <[email protected]> wrote in message
news:[email protected]...
> Hi Paul, this worked great! Thank you so much. I have one more question,
> since my "Count" calculation is going into a currency formated column, how
> can I have that one cell be bolded text. Its showing as currency now.
>
> "Paul B" wrote:
>
> > SAITCFanTN, try this,
> >
> > Sub addthem()
> > Dim rng As Range
> > Set rng = Cells(Rows.Count, 2).End(xlUp)
> >
> > rng.Offset(2, -1).Value = "Total"
> > rng.Offset(3, -1).Value = "Items"
> >
> > rng.Offset(2, 0).Value = Application.Sum(Range("B1", rng))
> > rng.Offset(3, 0).Value = Application.Count(Range("B1", rng))
> >
> > End Sub
> >
> >
> > --
> > Paul B
> > Always backup your data before trying something new
> > Please post any response to the newsgroups so others can benefit from it
> > Feedback on answers is always appreciated!
> > Using Excel 2002 & 2003
> >
> > "SITCFanTN" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a report that I download into Excel each day. The amount of
rows
> > vary
> > > each day. I want to add to an existing macro code that will add at
the
> > end
> > > of the report (regardless of how many rows there are), in column the
text
> > of
> > > "Total" and then add the sum of column B (currency) and place that
total
> > in
> > > column B on the same row as Total. On the next row I would like to
add
> > the
> > > text "Items" in column A and then have the count display in Column B.
I
> > just
> > > don't know the code to show this because of not being able to
designate
> > what
> > > row it will display on. I could have 1500 rows of data or 3000 rows
of
> > data.
> > > I would like to skip one row and then add the "Total" text and
> > calculation
> > > and below that at the Items count. I appreciate any help you can give
me.
> > > Thanks so much
> >
> >
> >