Think you missed how to use the collection to do this:

Dim x As New Collection
Dim y As Integer
y = 1

Do
If Cells(y, 4).Value = "Monday" Then
On Error Resume Next
x.Add Item:=Cells(y, 5).Value, cells(y,5).Text
On Error goto 0
End If
y = y + 1
Loop Until y = 1000

z = x.Count

--
Regards,
Tom Ogilvy


"mabond" wrote:

> Nigel
>
> Thanks, it was a push in the right direction I needed. Because of the layout
> of the exported file from the third party app the Filters was not an easy
> option....but the collection object came up with the result. I'm already
> looping through each line in the file so I included the following to identify
> unique dates
>
> Dim x As New Collection
> Dim y As Integer
> Dim MyObject
> y = 1
>
> Dim dateExists As Boolean
> dateExists = False
>
> x.Add Item:=Cells(4, 5).Value ' the first date is always in this cell
>
> Do
> If Cells(y, 4).Value = "Monday" Then
> dateExists = False
> For Each MyObject In x
> If MyObject = Cells(y, 5).Value Then
> dateExists = True
> End If
> Next MyObject
> If dateExists = False Then
> x.Add Item:=Cells(y, 5).Value
> End If
> End If
> y = y + 1
> Loop Until y = 1000
>
> z = x.Count
>
> Many thanks again
>
> Regards
>
> Michael Bond
>
> "Nigel" wrote:
>
> > Have you looked at using advanced filters? This can be configured to
> > produce a list of unique values that may provide the list you require.
> >
> > If not then you might consider using a Collection Object to build a unique
> > list
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "mabond" <mabond@discussions.microsoft.com> wrote in message
> > news:346217F7-4F08-4A66-AA77-D2FD8F9EE84E@microsoft.com...
> > > Hi all
> > >
> > > I'm hoping someone out there can suggest a way forward with a spreadsheet
> > > application Ive recently developed.
> > >
> > > The spreadsheet contains data from a third party application which in
> > > effect
> > > produces a "csv" file with the planned shifts for a group of employees. My
> > > application is designed to analyse the hours being worked in various
> > > categories from the data contained in the spreadsheet.
> > >
> > > The user has the option from the third party application to select more
> > > than
> > > one employee and more than one week. If that option is selected the output
> > > is
> > > sorted by employee then by date. So employee 1 has a number of lines under
> > > week 1 followed by a number of lines under week 2 followed by employee 2
> > > with
> > > week 1 and employee 2 with week 2 etc.
> > >
> > > Part of my analysis of the data requires that I know the number of weeks
> > > in
> > > the report. Presently that is done by way of a text box on the user form
> > > asking the user to select 1 or 2 or 3 etc.
> > >
> > > I'd prefer if the number of weeks could be identified programatically so
> > > my
> > > question is :
> > >
> > > Is there a way we can count the number of unique values from all of the
> > > values in a column .....
> > >
> > > Emp1, 22/05/06
> > > Emp1, 29/05/06
> > > Emp2, 22/05/06
> > > Emp2, 29/05/06
> > >
> > > would give the result 2
> > >
> > > Note: the dates are not in consecutive rows. There is data in rows between
> > > the firstd ate for Emp1 and second date for Emp2 etc., however the only
> > > date
> > > format which exists in the column is that against the employee name. None
> > > of
> > > the other rows between employee names contains a date format
> > >
> > > I hope I've explained my problem and add that I'm happy with a suggestion
> > > for the principle of how to do it as I'm confident in my own coding to be
> > > able to move forward if I can just have an idea as to how I could appraoch
> > > it.
> > >
> > > Any help or suggestions would be greatly appreciated.
> > >
> > > Regards
> > >
> > > Michael
> > > in employee order
> >
> >
> >