Trouble getting Unique Dates from Range
 

The following code does a great job at pulling unique values from a range and
making them part of a list box. However, it cannot do date. For some
reason, it pulls every single date value. I have checked, and the dates, in
long form, are all set at 12:00pm, so it is not a fact of the times being
different. Any ideas?

Set rng = .Range(.Cells(2, MyI), .Cells(2 ^ 16, MyI).End(xlUp))

For Each cCell In rng
If cCell < "" Then
If IsError(Application.Match(cCell, UniqueList, 0)) Then
ReDim Preserve UniqueList(0 To counter)
UniqueList(counter) = cCell
counter = counter + 1
End If
End If
Next cCell

Trouble getting Unique Dates from Range
 

Maybe you could just look at the dateserial number:

For Each cCell In rng
If cCell.value < "" Then
If IsError(Application.Match(clng(cCell.value), UniqueList, 0)) Then
ReDim Preserve UniqueList(0 To counter)
UniqueList(counter) = clng(cCell.value)
counter = counter + 1
End If
End If
Next cCell

But now your array is full of longs. You'll have to format them the way you
want to see dates.

Mark wrote:

The following code does a great job at pulling unique values from a range and
making them part of a list box. However, it cannot do date. For some
reason, it pulls every single date value. I have checked, and the dates, in
long form, are all set at 12:00pm, so it is not a fact of the times being
different. Any ideas?

Set rng = .Range(.Cells(2, MyI), .Cells(2 ^ 16, MyI).End(xlUp))

For Each cCell In rng
If cCell < "" Then
If IsError(Application.Match(cCell, UniqueList, 0)) Then
ReDim Preserve UniqueList(0 To counter)
UniqueList(counter) = cCell
counter = counter + 1
End If
End If
Next cCell

--

Dave Peterson

Trouble getting Unique Dates from Range
 

Hi,

try this:

Dim UniqueList() As Variant

'...

UniqueList(counter) = cCell.Value2


the Match function (most other functions also) seems to convert a Date
type value to a text and compare with it. so any date and number get no
match in a vb array of date.

--
HTH,

okaizawa


Mark wrote:
The following code does a great job at pulling unique values from a range and
making them part of a list box. However, it cannot do date. For some
reason, it pulls every single date value. I have checked, and the dates, in
long form, are all set at 12:00pm, so it is not a fact of the times being
different. Any ideas?

Set rng = .Range(.Cells(2, MyI), .Cells(2 ^ 16, MyI).End(xlUp))

For Each cCell In rng
If cCell < "" Then
If IsError(Application.Match(cCell, UniqueList, 0)) Then
ReDim Preserve UniqueList(0 To counter)
UniqueList(counter) = cCell
counter = counter + 1
End If
End If
Next cCell

Trouble getting Unique Dates from Range
 

That certainly works, but not it's returning all of the dates in as unformated.

The difficulty is that I have this procedure evaluating a number of columns,
some have dates, others do not... how can I have items that were originally
dates return as dates.

"okaizawa" wrote:

Hi,

try this:

Dim UniqueList() As Variant

'...

UniqueList(counter) = cCell.Value2


the Match function (most other functions also) seems to convert a Date
type value to a text and compare with it. so any date and number get no
match in a vb array of date.

--
HTH,

okaizawa


Mark wrote:
The following code does a great job at pulling unique values from a range and
making them part of a list box. However, it cannot do date. For some
reason, it pulls every single date value. I have checked, and the dates, in
long form, are all set at 12:00pm, so it is not a fact of the times being
different. Any ideas?

Set rng = .Range(.Cells(2, MyI), .Cells(2 ^ 16, MyI).End(xlUp))

For Each cCell In rng
If cCell < "" Then
If IsError(Application.Match(cCell, UniqueList, 0)) Then
ReDim Preserve UniqueList(0 To counter)
UniqueList(counter) = cCell
counter = counter + 1
End If
End If
Next cCell

Trouble getting Unique Dates from Range
 

Mark wrote:
The difficulty is that I have this procedure evaluating a number of columns,
some have dates, others do not... how can I have items that were originally
dates return as dates.

in that case, you don't have to use the match function for a vb array.
for example,

Dim UniqueList() As Variant
Dim cell_value As Variant
Dim i As Long

'...

For Each cCell In rng
cell_value = cCell.Value
If Not IsError(cell_value) Then
If cell_value < "" Then
For i = 0 To counter - 1
If UniqueList(i) = cell_value Then Exit For
Next
If i counter - 1 Then
ReDim Preserve UniqueList(0 To counter)
UniqueList(counter) = cell_value
counter = counter + 1
End If
End If
End If
Next cCell

--
HTH,

okaizawa