Storage of a serie of dates

[daniroy]

Hi all, and first of all thank you very much once again for your help.

In the spreadsheet I am using at the moment, dates are stored in
ascending order such as

22 april 2006
23 may 2006
11 june 2006
12 july 2006
and so on....

This dates repeat themselve an unequal number of times, thus
lookingsuch as

22 april 2006
22 april 2006
22 april 2006
22 april 2006
23 may 2006
23 may 2006
11 june 2006
11 june 2006
11 june 2006
11 june 2006
11 june 2006
11 june 2006
12 july 2006
12 july 2006
and so on ...

What I need is to allocate every date to a variable, such as

Sub Define_Dates()

Dim FirstExpiry As Variant
Dim SecondExpiry As Variant
Dim ThirdExpiry As Variant
- and so on -

FirstExpiry = Worksheets("ID").Range("D4").Value
if "D4" is the first line where 22 april 2006 is mentioned.

But if so, how can I code anything in order to go through the list date
and define the on cell "D25" or "D29" or whatever, a new date is
inputed and thus should be used in order to define SecondExpiry =
Worksheets("ID").Range("D29").Value ?

I do not have a clue if I am clear or not. What I want is a code able
to define FirstExpiry as the first date it meet when screening D
column, then SecondExpiry as the second date met while screening the
column and so on ...

Thank you very much for any help. Obviously if I am not clear enough
please feel absolutely free to ask me any other information.

I wish you all the best
Daniel ROY

 

[Ivan Raiminius]

Hi,

I am not sure, if I understood your question, but in my opinion you
need to add all the dates into a collection (which allows you to add
only unique members).

Regards,

Ivan

 

[Guest]

A collection would be a good approach.

Sub abc()
Dim expiry As New Collection
With Worksheets("sheet1")
Set rng = Range(.Cells(4, "D"), .Cells(4, "D").End(xlDown))
End With
On Error Resume Next
For Each cell In rng
expiry.Add cell.Value, Format(cell.Value, "yyyymmdd")
Next
On Error GoTo 0
MsgBox expiry(1)
Mstbox expiry(expiry.count)
End Sub

so expiry.count gives you the number of unique dates. expiry(n) gives you
the nth date (n can't exceed expiry.count)

 

[daniroy]

thank you very much Tom for your help, as for Ivan.
I did implement the following code which is running smoothly. Anyway,
it does not really store the individual dates, does it? I am actually
seing a MsgBox opening displaying the first date and then the last
date. To give you more colors, the column is containing 490 dates
sorted one after the other, but only six different dates as first one
is going to repeat itself 62 times, second one 39 times and so on, and
I would need to store the six individualy different dates as variables
in order to redisplay them later in subsequent calculation.
best regards and thanks again
Daniel

Sub abc()

Dim expiry As New Collection
Worksheets("ESX").Select
With Worksheets("ESX")
Set rng = Range(.Cells(10, "F"), .Cells(10, "F").End(xlDown))
End With
On Error Resume Next
For Each cell In rng
expiry.Add cell.Value, Format(cell.Value, "yyyymmdd")
Next
On Error GoTo 0
MsgBox expiry(1)
MsgBox expiry(expiry.Count)
End Sub

 

[Ivan Raiminius]

Hi Daniel,

It stores all the dates as members of the collection.

You can see in code from Tom:

MsgBox expiry(1)
MsgBox expiry(expiry.Count)

Which displays the first member of the collection (1) and the last
member (expiry.count). If you want to see all members, you should
iterate the collection like this:

for i=1 to expiry.count
msgbox expiry(i)
next i

Regards,
Ivan

 

[daniroy]

thanks again Ivan, yes indeed I understand a lot better now, that is
what is great with this place !
I now use the following code which is running just fine, if I have 9
members in the collection.
But I can have 12 as 3, the number of members is a variable. And I
recon to code that is well above my abilities.
I need to input dates on ID worksheet, but how to input the exact
number of different dates actually included in the ESX worksheet?
regards and thanks again,
yours
Daniel


Sub abc()

Dim expiry As New Collection
Worksheets("ESX").Select
With Worksheets("ESX")
Set rng = Range(.Cells(10, "F"), .Cells(10, "F").End(xlDown))
End With
On Error Resume Next
For Each cell In rng
expiry.Add cell.Value, Format(cell.Value, "yyyymmdd")
Next
On Error GoTo 0

Worksheets("ID").Range("H23").Value = expiry(1)
Worksheets("ID").Range("H24").Value = expiry(2)
Worksheets("ID").Range("H25").Value = expiry(3)
Worksheets("ID").Range("H26").Value = expiry(4)
Worksheets("ID").Range("H27").Value = expiry(5)
Worksheets("ID").Range("H28").Value = expiry(6)
Worksheets("ID").Range("H29").Value = expiry(7)
Worksheets("ID").Range("H30").Value = expiry(8)
Worksheets("ID").Range("H31").Value = expiry(9)

End Sub

 

[Guest]

Thanks Ivan.

Just to add for daniroy, I used the message boxes just to show you how you
can access the dates that are stored in the collection.

 

[Ivan Raiminius]

Hi Dani,

change your code as follows:

Sub abc()
Dim i as long
Dim expiry As New Collection
Worksheets("ESX").Select
With Worksheets("ESX")
Set rng = Range(.Cells(10, "F"), .Cells(10, "F").End(xlDown))
End With
On Error Resume Next
For Each cell In rng
expiry.Add cell.Value, Format(cell.Value, "yyyymmdd")
Next
On Error GoTo 0

for i=1 to expiry.count
Worksheets("ID").Range("H23").cells(i,1).Value = expiry(i)
next i

end sub


Added one row of code in the beginning (dim i as long), and three rows
at the end replacing nine rows of your code.

Regards,

Ivan

 

[Guest]

Sub abc()

Dim expiry As New Collection
Worksheets("ESX").Select
With Worksheets("ESX")
Set rng = Range(.Cells(10, "F"), .Cells(10, "F").End(xlDown))
End With
On Error Resume Next
For Each cell In rng
expiry.Add cell.Value, Format(cell.Value, "yyyymmdd")
Next
On Error GoTo 0
for i = 1 to expiry.count
Worksheets("ID").cells(i+22,"H").Value = expiry(i)
Next

End Sub

 

[daniroy]

Ivan, Tom, you are close to God in VB.
my grateful admiration
Daniel