Searching a column of time data in the given format(ex: 12/8/2015 9:30:53 AM) for data pt

Hi acopp24 and welcome to ExcelForum,

To solve your problem you first have to understand how Excel stores dates and times. Dates are stored as whole numbers and Time of day is stored as a fraction of a day when the 'Date' type is used (which stores data the same as type 'Double').
For example:

Formula:

Please Login or Register  to view this content.

Chances are you will never be successful trying to find '9:30:53 AM' as '9:30' entered by the user using the .Find command due to the way Excel stores the data.

Without seeing your file, my speculation is that the most likely chances of success would be to scan your data one cell at a time and convert each time to a string (e.g. Format(Range("A3"),"h:mm") to see if there is a match to '9:30'.

I suggest you upload a sample file if additional help is needed.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Lewis

I added a file with the times and one of the columns of data. The overall application is more complex than this operation so I'm not sure an "After" file will be helpful. Would vlookup be a more successful approach? I was able to loop through the data and convert it to strings, but the find function is still unsuccessful.

Hi,

Thanks for the rep points.

See the attached file which contains 3 of many alternatives to match the time:
a. Brute Force
b. .Find / .FindNext using a Helper Column (times converted to text)
c. Index/Match then Brute Force using a Helper Column (times converted to text)

Complete code follows below in ordinary code module ModSearchDate:

Please Login or Register  to view this content.

Lewis

Hi,

See the attached file which uses the following code. I added additional cells on the Spreadsheet for:
a. Number of rows to copy before the first match
b. Number of rows to copy after the first match
c. Destination sheet Name

In ordinary code Module ModFindFirstMatch:

Please Login or Register  to view this content.

To see how the code works, I suggest 'Single Stepping' through the code after the Match is found.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

To manually set a breakpoint, see http://www.wiseowl.co.uk/blog/s196/breakpoints.htm

Please let me know if you need additional help.

Lewis

LJMetzger,

Thank you for your help. Your code works well and I was able to adjust it for my specific application. I have some other questions, but I am going to start a new thread as they are not related to the initial question.