PDA

View Full Version : VBA date macro



wakwak1
01-02-2008, 09:44 PM
I want to check if a cell has a value of, say, 10:00:00 AM, and then a loop will be entered into.


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


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
01-02-2008, 10:24 PM
If ActiveCell.Offset(0,2) contains a serial date, it's type Double or Date.
.
Do While ActiveCell.Offset(0, 2).Value = TimeValue("10:00:00 AM")

Bob Phillips
01-03-2008, 02:30 AM
If the cell is formatted in exactly that way, you could test the text property



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

wakwak1
01-12-2008, 04:26 AM
Thanks!

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

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
01-12-2008, 09:53 AM
Time is a VBA date function as well.

It returns, well, the current time.

Bob Phillips
01-12-2008, 10:03 AM
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
01-12-2008, 04:26 PM
Thanks !

wakwak1
01-12-2008, 06:45 PM
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:
For i = 1 to 12
time = time + "0:" & Cells(2, k)
k = k + 1
Next i

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
01-13-2008, 03:31 AM
For i = 1 To 12
time = time + TimeSerial(0, 0, Cells(2, i + 3))
Next i