Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find time
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. -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find time
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 -----Original Message----- 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. -- Jim Rech Excel MVP . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find time
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 -----Original Message----- 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 -----Original Message----- 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. -- Jim Rech Excel MVP . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Best Available time | Excel Worksheet Functions | |||
find the difference between start time and end time when spanning. | Excel Discussion (Misc queries) | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
How can i find time in various time zone? | New Users to Excel |