|
Find end of week and insert Data
I am trying to count dates in column A and at the end of every work week (monday thru Friday) automatically enter "Weekly Totals" in the row below the last date of the week in column A EXAMPLE: Example: Date 01/01/06 01/01/06 01/02/06 01/03/06 01/03/06 Weekly Totals 01/08/06 01/09/06 01/10/06 01/11/06 01/11/06 01/12/06 Weekly Totals Can anyone Help? -- parteegolfer ------------------------------------------------------------------------ parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=517623 |
Find end of week and insert Data
Dim rng as Range
set rng = cells(rows.count,1).End(xlup)(2) now use rng. It is the cell in column A on the row for the totals. -- Regards, Tom Ogilvy "parteegolfer" wrote in message news:parteegolfer.23z1ob_1141184102.6927@excelforu m-nospam.com... I am trying to count dates in column A and at the end of every work week (monday thru Friday) automatically enter "Weekly Totals" in the row below the last date of the week in column A EXAMPLE: Example: Date 01/01/06 01/01/06 01/02/06 01/03/06 01/03/06 Weekly Totals 01/08/06 01/09/06 01/10/06 01/11/06 01/11/06 01/12/06 Weekly Totals Can anyone Help? -- parteegolfer ------------------------------------------------------------------------ parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=517623 |
Find end of week and insert Data
Where do I put this? In the VBA Project? also will this enter "Weekly Totals" in the cell below the last date of the week in column A? -- parteegolfer ------------------------------------------------------------------------ parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=517623 |
Find end of week and insert Data
Tom's solution will only add a grand total, no weekly subtotals.
You'll have to build a helper column which will hold the week bo. corresponding to each date. Then you can use DataSubtotals HTH -- AP "Tom Ogilvy" a écrit dans le message de ... Dim rng as Range set rng = cells(rows.count,1).End(xlup)(2) now use rng. It is the cell in column A on the row for the totals. -- Regards, Tom Ogilvy "parteegolfer" wrote in message news:parteegolfer.23z1ob_1141184102.6927@excelforu m-nospam.com... I am trying to count dates in column A and at the end of every work week (monday thru Friday) automatically enter "Weekly Totals" in the row below the last date of the week in column A EXAMPLE: Example: Date 01/01/06 01/01/06 01/02/06 01/03/06 01/03/06 Weekly Totals 01/08/06 01/09/06 01/10/06 01/11/06 01/11/06 01/12/06 Weekly Totals Can anyone Help? -- parteegolfer ------------------------------------------------------------------------ parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=517623 |
Find end of week and insert Data
Assuming data are populated in ascending order starting at A2 and
there is no blank cells, no Saturday and no Sunday. then try this one. Sub weekdaycount() Dim wrng As Range, lrng As Range Dim count As Long Set wrng = Cells(2, "a") '<<=== start range - change if need Set lrng = Cells(Cells.Rows.count, "a").End(xlUp) Do While (wrng.Row <= lrng.Row) count = 1 Do While (Weekday(wrng) <= Weekday(wrng(2))) If wrng(2) < "" Then Set wrng = wrng(2) count = count + 1 Else Exit Do End If Loop Set wrng = wrng(2) wrng.EntireRow.Insert wrng(0) = "Weekly Totals" wrng(0, 2) = count Loop End Sub keizi "parteegolfer" wrote in message news:parteegolfer.23z1ob_1141184102.6927@excelforu m-nospam.com... I am trying to count dates in column A and at the end of every work week (monday thru Friday) automatically enter "Weekly Totals" in the row below the last date of the week in column A EXAMPLE: Example: Date 01/01/06 01/01/06 01/02/06 01/03/06 01/03/06 Weekly Totals 01/08/06 01/09/06 01/10/06 01/11/06 01/11/06 01/12/06 Weekly Totals Can anyone Help? -- parteegolfer ---------------------------------------------------------------------- -- parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=517623 |
Find end of week and insert Data
Depends on the interpretation of the question. I understood him to want to
enter the total on each friday as the occur as an example. If he has a list of several months data and address it all at one time, then what you suggest is most appropriate. -- Regards, Tom Ogilvy "Ardus Petus" wrote in message ... Tom's solution will only add a grand total, no weekly subtotals. You'll have to build a helper column which will hold the week bo. corresponding to each date. Then you can use DataSubtotals HTH -- AP "Tom Ogilvy" a écrit dans le message de ... Dim rng as Range set rng = cells(rows.count,1).End(xlup)(2) now use rng. It is the cell in column A on the row for the totals. -- Regards, Tom Ogilvy "parteegolfer" wrote in message news:parteegolfer.23z1ob_1141184102.6927@excelforu m-nospam.com... I am trying to count dates in column A and at the end of every work week (monday thru Friday) automatically enter "Weekly Totals" in the row below the last date of the week in column A EXAMPLE: Example: Date 01/01/06 01/01/06 01/02/06 01/03/06 01/03/06 Weekly Totals 01/08/06 01/09/06 01/10/06 01/11/06 01/11/06 01/12/06 Weekly Totals Can anyone Help? -- parteegolfer ------------------------------------------------------------------------ parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=517623 |
| All times are GMT +1. The time now is 08:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com