Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique dates within each unique ID | Excel Worksheet Functions | |||
Identifying unique dates in a row of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
Trouble with dates...... | Excel Worksheet Functions |