| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to insert day of the week | Excel Discussion (Misc queries) | |||
| how to insert day of the week | Excel Worksheet Functions | |||
| Macro To Find Data And Insert Row | Excel Programming | |||
| Want day of the week when I insert a date | New Users to Excel | |||
| How do I find data from a list (or table) and insert it in a row? | Excel Discussion (Misc queries) | |||
Linear Mode
