Find time

[Martin Los]

In column A I have:
Cell Content
A1 Hour
A2 7:00
A3 7:30
A4 8:00
A5 8:30
A6 9:00
A7 9:30
A8 10:00
A9 10:30
A10 11:00
A11 11:30
A12 12:00
A13 12:30
A14 13:00
A15 13:30
A16 14:00
A17 14:30
A18 15:00
A19 15:30
A20 16:00
A21 16:30
A22 17:00
A23 17:30
A24 18:00
A25 18:30
A26 19:00
A27 19:30
A28 20:00
A29 20:30
A30 21:00
A31 21:30
A32 22:00
A33 22:30
A34 23:00

In VBA macro I have:
Sub prueba()
Columns("A:A").EntireColumn.Select
On Error Resume Next

intRowBegin = 0
With Selection
intRowBegin = .Cells.Find(What:="22:30", _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Debug.Print intRowBegin
End With
End Sub

The result is that intRowBegin = 0 for all values from
12:30 until 23:00. These timevalues will NOT be found with
the find command. Values from 07:00 until 12:30 do get
find with the macro.

What am I doing wrong? Any help apreciated?

TIA Martin

 

[Jim Rech]

Excel's Edit, Find function compares the find string to the actual contents
of the cells. The actual contents of the cell may be different than the
value of the cell as it is displayed due to formatting. If you select the
cell that displays "20:30" and look at the formula bar you will see what is
actually in the cell. This is the value you must do your Find with. In my
case it is "8:30:00 PM", however if you have different Windows Regional
Settings you may see something different.

 

[Guest]

Dear Jim:

The funny thing is that the code in the macro is exactly
the code I recorded with the macro recorder!

The formula bar actually shows "22:30:00" for the time
that appears as "22:30". If I use a "find" query (ctlr-f)
in the workbook with "22:30", Excel finds the value
without a problem (since it forms part of the time value
h:mm "22:30:00").

However, if I try to use the recorded macro in VBA (going
through it with F8), I get a runtime error no. 91 ("object
variable or block With not established").
PS. I use a Spanish Excel 2002 version with a Spanish
Windows XP.

I still can´t figure out why things do not work from 12:30
until 23:00, and do work from 7:00 til 12:30!!

Any help appreciated!

Martin

 

[Martin Los]

After having tried several things, I finally found the
SIMPLE answer:

LookIn:=xlFormulas has to be changed to
LookIn:=xlValues

and then everything works just fine!

Thanks for your help Jim, you helped on the way to solving
it!

Martin