Solved: Finding the hour prior to original hour
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...
[vba]
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
[/vba]
And I may end up going to the date before to find the time but I also want to track the missig hours...