Collection Not Working with Date and Time

[Felix1980]

I have a column of date and times like so :

I have a collection that loops through all 527,000 rows to collect all these times. First time I tried, I noticed that it was only holding the date :

For Each DDateRng In Destws.Range("A2:A" & DestLRow)
    Timecoll.Add Destws.Cells(DDateRng.Row, 1).Value
Next DDateRng

so I added Cstr hoping it would work to save it as a string :

For Each DDateRng In Destws.Range("A2:A" & DestLRow)
    Timecoll.Add CStr(Destws.Cells(DDateRng.Row, 1).Value)
Next DDateRng

I can see that this is holding the data but when I try to search for the index of one, it always says tchange = string date (good), tReturn = -1 :

For Each t In Sourcews.Range("H2:H" & SourceLRow)
    tchange = CStr(t.Value)
    tReturn = Timecoll.indexof(tchange, 0)
    eReturn = SEmpcoll.indexof(t.Offset(0, -6).Value, 0)
    If tReturn = -1 Then
    'do something?
    Else
        Destws.Cells(tReturn, eReturn).Value = t.Offset(0, 4).Value
    End If

The above code is looping through another group of date times (below) and I'm trying to get it to search the date time from the source (t) in the collection of date times, find the index, then with that index and the index of the employee number put specific data in the cell and drag it down to the corresponding date time from the I column. The destination sheet looks like the above screen shot and the source data looks like below :

If someone could point out where I'm failing with the collection I would appreciate it.

 

[StephenCrump]

I have a collection that loops through all 527,000 rows to collect all these times. First time I tried, I noticed that it was only holding the date :

Are you sure about this first bit (I haven't read further yet) ...

Timecoll(1) will be 1/1/20 (which looks like only the date, but this is the same as 1/1/20 12:00 am)
Timecoll(2) will correctly show 1/01/2020 12:01:00 AM
Timecoll(3) will correctly show 1/01/2020 12:02:00 AM
... etc

 

[Felix1980]

You're correct, I didn't move past the first iteration when checking, my bad. However, the recall is still not working.

For Each DDateRng In Destws.Range("A2:A" & DestLRow)
    Timecoll.Add Destws.Cells(DDateRng.Row, 1).Value
Next DDateRng

For Each t In Sourcews.Range("H2:H" & SourceLRow)
    tReturn = Timecoll.indexof(t, 0)
    eReturn = SEmpcoll.indexof(t.Offset(0, -6).Value, 0)
    If tReturn = -1 Then
    'do something?
    Else
        Destws.Cells(tReturn, eReturn).Value = t.Offset(0, 4).Value
    End If
Next t

tReturn is still pulling in -1

Any suggestions?

 

[StephenCrump]

A VBA collection doesn't have an IndexOf method. If your code is getting past this line, I guess you must have previously set tReturn=-1, and have an On Error Resume Next statement (dangerous!)

I'm really not clear what you're trying to do. Can you perhaps illustrate with a simple example, rather than a description?

 

[Felix1980]

@StephenCrump :
I am working with :

Dim Timecoll as object
Set Timecoll = CreateObject("System.Collections.ArrayList")

I thought that meant it was a collection, if it's in fact an array please let me know.
Update :
I got it to work by doing a text to column on the destination date time column. Even though I inputed the data manually, and it auto-formatted it to date time, excel still did not see it as a date time. After text to column the following code works :

Dim Timecoll as object
Set Timecoll = CreateObject("System.Collections.ArrayList")

For Each DDateRng In Destws.Range("A2:A" & DestLRow)
    Timecoll.Add CStr(Destws.Cells(DDateRng.Row, 1).Value)
Next DDateRng

For Each t In Sourcews.Range("H2:H" & SourceLRow)
    tchange = CStr(t.Value)
    tReturn = Timecoll.indexof(tchange, 0) + 2
    eReturn = SEmpcoll.indexof(t.Offset(0, -6).Value, 0) + 2
    If tReturn = -1 Then
    'do something?
    Else
        Destws.Cells(tReturn, eReturn).Value = t.Offset(0, 4).Value
    End If
Next t

 

[StephenCrump]

Ahh! You're using an ArrayList.

Sorry I didn't help - I was stuck on thinking Collection (limited functionality compared to an ArrayList).

I'm glad you got it working.

 

[Felix1980]

Apologies! I am self-taught and don't understand all the names. Thank you, I will remember for next time these are ArrayList's and not a collection just because it has that word in the name!