Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Add totals at end of the report with unknow number of rows with VB
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Add totals at end of the report with unknow number of rows with VB
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Add totals at end of the report with unknow number of rows wit
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Add totals at end of the report with unknow number of rows wit
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a specified number of rows dynamically | Excel Worksheet Functions | |||
#REF! errors due to number of rows | New Users to Excel | |||
Charting when number of rows are greater than 65536 | Charts and Charting in Excel | |||
Applying auto-filter with large number of rows | Excel Discussion (Misc queries) | |||
How do I reduce number of Rows displayed in Excel worksheet? | Setting up and Configuration of Excel |