VBA date macro

[wakwak1]

I want to check if a cell has a value of, say, 10:00:00 AM, and then a loop will be entered into.

[vba]
Range("J756:J761").Select
Selection.Copy
Range("J762").Select
Do While IsEmpty(ActiveCell.Offset(0, -7)) = False
ActiveCell.Offset(1, 0).Select

Do While ActiveCell.Offset(0, 2) = "10:00:00 AM"

ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Loop

Loop
End Sub
[/vba]

The column with dates has cells with format h:mm:ss
However, I think the way I am referring to the date value is incorrect, because my code doesn't function the way I intended.

[mikerickson]

If ActiveCell.Offset(0,2) contains a serial date, it's type Double or Date.
.
[VBA]Do While ActiveCell.Offset(0, 2).Value = TimeValue("10:00:00 AM") [/VBA]

[Bob Phillips]

If the cell is formatted in exactly that way, you could test the text property

[vba]

Do While ActiveCell.Offset(0, 2).Text = "10:00:00 AM"
[/vba]

[wakwak1]

Thanks!

Sort of similar situation, now I have this code:
[vba]If ActiveCell.Offset(0, 1).Value > time Then[/vba]

time is a Date variable whilst the activecell.offset code refers to cells which are like 9:00:00 AM, 9:00:01 AM, etc. So VBA reads the ActiveCell.Offset(0,1).Value part as numerical eg "0.4666667" etc instead of as time format.

I tried using ActiveCell.Offset(0,1).Text ......... but this doesn't seem to work with the greater than operator ? Because then the argument looks like: 10:00:00 > 9:00:00 AM (when I hover over the relevant code in the VBA module/coding window)..............but it doesn't enter the IF function so i think it's got something to do with that A.M. part ?

[Norie]

Time is a VBA date function as well.

It returns, well, the current time.

[Bob Phillips]

That is because Excel stores time as a fraction of one (1 day). So 12:00pm is stored as 0.5, 8:00am is .33333, etc., so comparing the value against time will work okay.

[wakwak1]

Thanks !

[wakwak1]

Similar date/time query:

I have a time variable, initiallized at 9:59:00 AM and I want to increment it by the amount that is present in cells D2,E2, etc. (So D2 could be 1, E2 could be 2, F2 could be 5.......then in the first case it time would be 10:00:00 AM, in the 2nd case it would be 10:01:00 AM etc).

I tried something like:
[VBA] For i = 1 to 12
time = time + "0:" & Cells(2, k)
k = k + 1
Next i [/VBA]

But it gives me a mismatch error, because time is a "date" variable....and the cells(2,k) are also date format....but they have values like "01" , "02", etc.

Any thoughts?

[Bob Phillips]

[vba]

For i = 1 To 12
time = time + TimeSerial(0, 0, Cells(2, i + 3))
Next i
[/vba]