VBA get time

[Vanda_a]

Hello all

could you guys advise my below code please

dim c as range

For each c in range("D2:D100")
   If Time(hour(c)),minute(c))) > 9:30 then
   msgbox c & "is bigger"
   End if
next c

D2:D100 is date and time(ex: 11/08/2015 8:30:22)
How to use Time properly?

 

[VBA Geek]

I think you're after something like this:

For Each c In Range("D2:D100")
   If TimeSerial(Hour(c), Minute(c)) > #9:30:00 AM# Then
    MsgBox c.Address & " is bigger"
   End If
Next c

 

[Vanda_a]

I think you're after something like this:

For Each c In Range("D2:D100")
   If TimeSerial(Hour(c), Minute(c)) > #9:30:00 AM# Then
    MsgBox c.Address & " is bigger"
   End If
Next c

I got it working now. Thank you very much

 

[Rick Rothstein]

I think you're after something like this:

For Each c In Range("D2:D100")
   If TimeSerial(Hour(c), Minute(c)) > #9:30:00 AM# Then
    MsgBox c.Address & " is bigger"
   End If
Next c

Some may find the syntax odd, but this would also work...

For N = 1 To 99
  If Evaluate("MOD(D2:D100,1)>Time(9,30,0)")(N, 1) Then
    MsgBox "D" & N + 1 & " is bigger"
  End If
Next

 

[Vanda_a]

Some may find the syntax odd, but this would also work...

For N = 1 To 99
  If Evaluate("MOD(D2:D100,1)>Time(9,30,0)")(N, 1) Then
    MsgBox "D" & N + 1 & " is bigger"
  End If
Next

Thanks for the alternative.
One more question pls. Dim FP as ....? FP = TimeSerial(hour(c),minute(c),second(c)) or set FP = ....
i would like to have a shortcut.

 

[VBA Geek]

Some may find the syntax odd, but this would also work...

For N = 1 To 99
  If Evaluate("MOD(D2:D100,1)>Time(9,30,0)")(N, 1) Then
    MsgBox "D" & N + 1 & " is bigger"
  End If
Next

nice or you could run EVAL one time and then loop through the array..

 

[VBA Geek]

Thanks for the alternative.
One more question pls. Dim FP as ....? FP = TimeSerial(hour(c),minute(c),second(c)) or set FP = ....
i would like to have a shortcut.

Dim FP as Date

FP = TimeSerial(hour(c),minute(c),second(c))

or

FP = c.Value - Int(c.Value)

 

[Vanda_a]

Dim FP as Date

FP = TimeSerial(hour(c),minute(c),second(c))

or

FP = c.Value - Int(c.Value)

Thank you very much

Below code. Msgbox = 1.11212e...... Why it doesn't show time? FP = 9:00 AM then it should pop up 8:44 AM
MsgBox FP - TimeSerial(Hour(c), Minute(c) - 16, Second(c))

 

[VBA Geek]

MsgBox Format(FP,"hh:mm AM/PM")

 

[Rick Rothstein]

nice or you could run EVAL one time and then loop through the array..

Good point... it was almost 6am local time when I wrote that, just before I went to sleep for the "night", so I guess I wasn't thinking clearly at the time, but yes, you are right, there no need to repeat the Evaluation inside the loop.

Dim N As Long, TimeArray As Variant
......
......
TimeArray = Evaluate("MOD(D2:D100,1)>Time(9,30,0)")
For N = 1 To 99
  If TimeArray(N, 1) Then
    MsgBox "D" & N + 1 & " is bigger"
  End If
Next