Finding the hour prior to original hour

[jazzyt2u]

Hi, I'm having problems finding the correct way to code the following issue. I'm sure it's simple but my mind won't go there today...

I have three columns
Date Time Country

First I look for the specified date. Then I select all rows with that date.
Then I search for a specific hour. example 3:00:00 AM. Then I look for a specific Country.

There can be 10 rows or more with that time which is associated with different countries. If the country i'm looking for doesn't exsist in that hour I need to go to the hour prior and I also need to track what hour it was missing in. It may take me all the way to the date prior as well...



Dim MyDate, MyTime, My Country, NewTime
MyDate = 3/22/09 'These are really variables but didn't want to put all that code in
MyTime = "3:00:00 AM"
MyCountry = "USA" 'These are really variables....
Selection.Find(What:=mydate, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Select
Cells.Find(What:=MyTime, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Select
Cell.Find(What:=MyCountry, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Select
If ActiveCell.offset(0,-1) <> MyTime Then
' NewTime = MyTime - this is where I'm having problems...this is where I want 2:00:00 AM to become the new time _
without hard coding and it may end up being 11:00:00 PM the day before..
End If


And I may end up going to the date before to find the time but I also want to track the missig hours...

[GTO]

Greetings Jazzy,

I believe this should work w/o the date specified.


If ActiveCell.Offset(0, -1) <> MyTime Then
' NewTime = MyTime - this is where I'm having problems...this is where I
' want 2:00:00 AM to become the new time without hard coding and it may
' end up being 11:00:00 PM the day before..
Dim iHour As Long, iMinute As Long, iSecond As Long
iHour = Hour(ActiveCell.Offset(, -1))
iMinute = Minute(ActiveCell.Offset(, -1))
iSecond = Second(ActiveCell.Offset(, -1))
If Hour(ActiveCell.Offset(, -1)) - 1 < 0 Then
MsgBox TimeSerial(iHour + 23, iMinute, iSecond)
Else
MsgBox TimeSerial(iHour + -1, iMinute, iSecond)
End If
End If

If not, you may wish to post an example workbook (stripped of private/company data, but with fake data to correlate what should be happening).

Just by going by what is shown, I believe the first .Find fails if the value for mydate is not found in the current selection of the active sheet.

If this is found, then it jumps to the cell, then (w/o doing anything) jumps to the cell with (presuming exists) MyTime, then again jumps to MyCountry.

Maybe you left most of the code out, but as shown, I would think that finding MyCountry is the only necessary part.

Hope that makes sense and that this helps,

Mark

[jazzyt2u]

Thank you Soooooooooooooooooooo much that was VERY helpful....