amatuerish, not at all. Very well done and the way I would do it as well
until someone showed me how to really use a collection. Just trying to pass
along what I have learned in the past.

A dictionary object from the scripting runtime can be even better for many
of these situations, but in this case, I think a collection is sufficient.


--
regards,
Tom Ogilvy


"mabond" <mabond@discussions.microsoft.com> wrote in message
news:29899D91-DFC7-44DA-8E50-7046EF9B9A75@microsoft.com...
> Tom
>
> don't bother replying to my question. I coded it with your suggestion and
it
> worked as I need it to. Thanks for your additional guidance here. Very
neat
> solution compared with my amatuerish attempt.
>
> Regards
>
> Michael Bond
>
>
>
> "Tom Ogilvy" wrote:
>
> > 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
> > > >
> > > >
> > > >