CODEWITHSUNDEEP
vba
gabriel
gabriel

Reputation: 399

Finding time periods in VBA

Date        Time    Variable
2011.01.02  22:22   1258.25
2011.01.02  22:24   1258.5
2011.01.02  22:26   1258.25
2011.01.02  22:27   1258.25
2011.01.02  22:28   1258.5
2011.01.02  22:29   1258.5
2011.01.02  22:31   1258.5
2011.01.02  22:32   1260.5
2011.01.02  22:33   1259.75
2011.01.02  22:34   1259.75
2011.01.02  22:37   1259.75
2011.01.02  22:38   1260
2011.01.02  22:39   1260.25
2011.01.02  22:40   1260
2011.01.02  22:43   1259.75
2011.01.02  22:44   1259.5
2011.01.02  22:47   1259.75
2011.01.02  22:48   1260
2011.01.02  22:52   1259.75
2011.01.02  23:10   1259.75
2011.01.02  23:12   1260
2011.01.02  23:13   1260.25
2011.01.02  23:14   1260.25
2011.01.02  23:15   1260.5
2011.01.02  23:16   1260.5
2011.01.02  23:17   1260.25

Hey guys,

I have a time series data in excel, and I would like to search for all "time" values that start with 23:xx (23 hour) and select that cell. Instead what I am getting is VBA selecting any cell that that have time "XX:23" (23rd minute)

Assuming I have my active cell on the "Time" column, I am using this code, buts its not working. What am I doing wrong?

Range(ActiveCell, ActiveCell.End(xlDown)).Find("23:", lookat:=xlPart).Select

Followup Question:

Also another quick question Doug, I would like to be able to find all the data points that start with "23:XX" the 23rd hour, find the first value and the last value, highlight everything in between and name it as a range.

I was thinking of a "Do" statement to cycle through the cells after the first "23:XX" is found

Do
     ActiveCell.Offset(1, 0).Select
Loop Until Left(ActiveCell) = "23:"

but it seems the "Left" function recognizes the cells as numeric, not as a time formated cell? What should I do? Thank you so much!!

Upvotes: 1

Views: 684

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

The Lookin argument of the FIND function specifies whether to look at the "Formula" - the text that you see in Edit Mode - orValue - what's displayed in the cells. Your FIND function is looking in the formulas, so it sees the "11:10 PM" within the cell. Change it to look in the values, like so:

Range(ActiveCell, ActiveCell..End(xlDown)).Find("23:", lookat:=xlPart,lookin:=xlvalues).Select

Upvotes: 2

Related Questions