Application.Match with Times

[Pat]

I've been at this problem for a while now, and could not
find any guidance in previous posts.

I have a range 6 X 10 of times that coorespond to a
schedule.
Start Break MealStart MealStop Break End
10:30 AM 12:00 PM 2:30 PM 3:30 PM 5:00 PM
7:00 PM

I then have a single row of times from midnight to 11:45
PM, in incriments
of 15 minutes.
12:00 AM 12:15 AM 12:30 AM 12:45 AM 1:00
AM etc

I am writing a macro that will iterate cell by cell
through the schedule
times and return the cooresponding column number matching
the time in the
single row (range named as "times").

Dim rng as Range
Dim TimeToFind as Double
Dim res as Variant
Dim CurRow, CurCol as Integer

CurCol = 1
CurRow = 1
Set rng = Range("times")
TimeToFind = CDbl(Cells(CurRow, CurCol))
res = Application.Match(TimeToFind, rng, 0)

Certain times are not being found, despite that they are
clearly there! It
has no trouble finding 10:30 AM (serial: 0.4375), or 12:00
PM (serial: 0.5).
It pukes on 2:30 PM.

Can anyone shed some light on this or point me in the
right direction?
Thanks for your assistance!

 

[Dave Peterson]

Since you're converting your times to double, how about converting the other
range("Times") to double, too:

Option Explicit
Sub testme01()

'Dim rng As Range
Dim TimeToFind As Double
Dim res As Variant
Dim CurRow, CurCol As Integer
Dim TimesArray As Variant

CurCol = 1
CurRow = 1
TimesArray = Range("times").Value

For CurCol = 1 To Cells(CurRow, Columns.Count).End(xlToLeft).Column
TimeToFind = CDbl(Cells(CurRow, CurCol))
res = Application.Match(TimeToFind, TimesArray, 0)
If IsError(res) Then
MsgBox "not found"
Else
MsgBox res
End If
Next CurCol

End Sub

(I tested it and it worked ok for

 

[Pat]

I've found the contributing problem. When creating my table of schedule
times, I selected one row, then dragged the times down to increment them.
It seems this is the problem. If I key the exact same time, I have no
problem finding a match.

However, my new challenge is figuring out why this is the case. Please try
the following experiment:

create a range of cells with the following columns
start break meal break2 end

enter one row of times
drag each cell down a series of rows to increment the time by one hour

Try to find a match of these times against an array of times elsewhere.

The row that was keyed will find matches. On rows where the cell value was
dragged, there will be an N/A error.

Further confusing is that values keyed and values dragged are equal....
a1 = 10:30 AM (keyed)
b1 = 11:30 AM (having dragged the value from the cell above)
a2 = 10:30 AM (keyed)
b2 = 11:30 AM (keyed)

If(value(b1)=value(b2), true, false) will return true.

I thought that perhaps Excel was incrementing the date as well, but the
value test disproved this.

Please help me understand this! It makes my brain hurt!

 

[Dave Peterson]

Dragging down numbers in excel can induce rounding errors. It's not anything
you did wrong. It's the way excel works.

You may want to use formulas instead of fill.

 

[Pat]

Does MATCH use something other than Value() in it's comparison?

 

[Dave Peterson]

I'm not sure. I know that I've had trouble with dates. Converting the values
(01/01/2004) to the serial dates sometimes makes a lot of difference inside VBA.

(never had the same problem in the worksheet, though.)

Does MATCH use something other than Value() in it's comparison?

 

[Patrick Molloy]

The issue is most likely because time is saved internally
as decimal part of a day. so 14:30 is 14.5 hoyrs or
14.5/24 => 0.6041666 recurring

You might consider entering th etime in the lookup column
as text ? ie '14:30
not ideal I'm afraid.

Patrick Molloy
Microsoft Excel MVP

 

[Daniel.M]

Hello,

Why not round the value AND the searched ranged?

Sub TimesAgain()
Dim C As Range
Dim rng As Variant
Dim TimeToFind As Double
Dim res As Variant

rng = Evaluate("=Transpose(ROUND(times,6))") ' Done once

For Each C In Range("A10:F10") ' ajust for the searched values
TimeToFind = Round(C, 6)
res = Application.Match(TimeToFind, rng, 0)
' here do something more meaningful
Debug.Print C.Address, IIf(IsError(res), "Not matched", res)
Next C

End Sub

Regards,

Daniel M.