Find end of week and insert Data

[parteegolfer]

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?

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[parteegolfer]

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?

[Ardus Petus]

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 Data>Subtotals

HTH
--
AP

"Tom Ogilvy" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

[kounoike]

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"
<[email protected]> wrote in
message
news:[email protected]...
>
> 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
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 Data>Subtotals
>
> HTH
> --
> AP
>
> "Tom Ogilvy" <[email protected]> a écrit dans le message de
> news:[email protected]...
> > 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"
<[email protected]>
> > wrote in message
> > news:[email protected]...
> > >
> > > 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
> > >
> >
> >
>
>