NoDupes with ComboBox Fill > Date Problem?

[I_AM]

Hi,
In Column A I have Dates entered thus-01/01/04,02/01/04 etc.. but formatted to show-Jan-04. The following Code will Show 2 entries of Jan-04 in ComboBox1.

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim ListSort As Range

With Sheet1
.Activate
Range(("A2"), Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).Activate
Selection.Activate
On Error Resume Next
For Each Cell In Selection
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
For Each Item In NoDupes
Main_Menu.ComboBox1.AddItem Format(Item, "[$-409]mmm-yy;@")
Next Item

How to get the Code to display only one entry?

Thanks Regards Ron

 

[Andrew Poulsom]

I thought you had a solution here:

http://216.92.17.166/board2/viewtopic.php?t=74692

You need:

NoDupes.Add Cell.Value, Cell.Text

 

[I_AM]

Yes So did I! Just couldnt rid myself of the multiple entries. (Not through lack of trying though) Will console myself by at least playing around with the correct line! Works a treat Thanks
Regards Ron

 

[CraigM]

Try:

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim ListSort As Range
Dim FoundDupe As Boolean
Dim n As Long
Dim CellDate As Variant

For Each Cell In Sheet1.Range(("A2"), Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
CellDate = Month(Cell) & "/" & Year(Cell)
FoundDupe = False
If Cell.Address <> "$A$2" Then
For n = 1 To NoDupes.Count
If CellDate = NoDupes.Item Then FoundDupe = True
Next n
End If
If FoundDupe = False Then NoDupes.Add CellDate
Next Cell

For Each Item In NoDupes
Main_Menu.ComboBox1.AddItem Format(Item, "[$-409]mmm-yy;@")
Next Item


Or, if you don't need to use your NoDupes collection somewhere else, you can get rid of it altogether, using:

Dim AllCells As Range, Cell As Range
Dim ListSort As Range
Dim FoundDupe As Boolean
Dim n As Long
Dim CellDate As Variant

For Each Cell In Sheet1.Range(("A2"), Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
CellDate = Format(Month(Cell) & "/" & Year(Cell), "[$-409]mmm-yy;@")
FoundDupe = False
If Cell.Address <> "$A$2" Then
For n = 0 To Main_Menu.ComboBox1.ListCount - 1
If CellDate = Main_Menu.ComboBox1.List Then FoundDupe = True
Next n
End If
If FoundDupe = False Then Main_Menu.ComboBox1.AddItem CellDate 'Format(CellDate, "[$-409]mmm-yy;@")
Next Cell

PS There are a few extra declarations that are not necessary, but I'm assuming you are using the variables somewhere else.