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]...

 

[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

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

 

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

 

[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.