application.match failing when it shouldn't

[Luke777]

Hi all,

I'm using the following piece of code to find a match

rotaRow = Application.Match(acSTime, rotaTime, 0)

When the locals window shows the below (and other examples) it works perfectly.

: acSTime : 44725.2708333333 : Double
: rotaTime(1,1) : 44725.2708333333 : Variant/Double

but when I get to the following, it fails to find a match.

: acSTime : 44725.3333333333 : Double
: rotaTime(7,1) : 44725.3333333333 : Variant/Double

it runs fine for the first 17 times but fails on the above for some reason.

Full code code looks like the below

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim acName As String
    Dim acSTime As Double, acFTime As Double
    Dim acSlots As Integer
    Dim eNumber As Long
    
    Set ws1 = Worksheets(1)
    Set ws2 = Worksheets(4)
    
    rotaENs = ws1.Range("C1", ws1.Range("C1").End(xlToRight))
    rotaTime = ws1.Range("A4", ws1.Range("A4").End(xlDown))
    
    For Each en In ws2.Range("A1", ws2.Range("A1").End(xlDown))
        eNumber = en.Offset(, 0)
        acName = en.Offset(, 12)
        acSTime = en.Offset(, 10)
        acFTime = en.Offset(, 11)
        acSlots = (acFTime - acSTime) * 96
        
        rCol = Application.Match(eNumber, rotaENs, 0)
        rRow = Application.Match(acSTime, rotaTime, 0)
        
    Next

Any ideas?

 

[Phuoc]

You can use the ROUND function to fix it:

eNumber = ROUND(en.Offset(, 0).Value, 10)

acName = ROUND(en.Offset(, 12).Value, 10)

...

 

[Luke777]

You can use the ROUND function to fix it:

eNumber = ROUND(en.Offset(, 0).Value, 10)

acName = ROUND(en.Offset(, 12).Value, 10)

...

any idea what place I need to round it to? I'm dealing with dates and times down to 15 minute intervals - so 11/06/2022 01:00:00 followed by 11/06/2022 01:15:00 doesn't need to be more granular than that

 

[Luke777]

strangely changing

        rRow = Application.Match(acSTime, rotaTime, 0)

to

        rRow = Application.Match(acSTime, rotaTime, 1)

seems to fix the issue without breaking anything further along.