hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes 0.39583333,
the time serial code.
but its still useless when I write if range(M2).value = 0.39583333 then DO
SOMETHING




thnks.

Hi,

How about this:

Sub Macro4()
If Range("A1").Value = TimeSerial(9, 30, 0) Then _
MsgBox Range("A1").Value
End Sub

BTW if you cross-post, chances are that you don't see some replies or people
post their replies when you already got an answer, i.e. waste their time.

Regards,
KL



<a wrote in message ...
hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes 0.39583333,
the time serial code.
but its still useless when I write if range(M2).value = 0.39583333 then
DO SOMETHING




thnks.

thanks. alot,
pretty new to usenet,,, so is it ok to cross post by attaching the next
group to CC?

one minor question.
how do you include special characters in formula? i am tryign to achieve
something like 9: 30.
with the semi-colon separating hours and mins.

Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])"




"KL" wrote in message
...
Hi,

How about this:

Sub Macro4()
If Range("A1").Value = TimeSerial(9, 30, 0) Then _
MsgBox Range("A1").Value
End Sub

BTW if you cross-post, chances are that you don't see some replies or
people post their replies when you already got an answer, i.e. waste their
time.

Regards,
KL



<a wrote in message ...
hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes
0.39583333, the time serial code.
but its still useless when I write if range(M2).value = 0.39583333 then
DO SOMETHING




thnks.

Use double quotes instead of apostrophes.

Range("N2").FormulaR1C1 = "=HOUR(RC[-1]" & ":" & MINUTE(RC[-1])"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



<a wrote in message ...
thanks. alot,
pretty new to usenet,,, so is it ok to cross post by attaching
the next group to CC?

one minor question.
how do you include special characters in formula? i am
tryign to achieve something like 9: 30.
with the semi-colon separating hours and mins.

Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])"




"KL" wrote in message
...
Hi,

How about this:

Sub Macro4()
If Range("A1").Value = TimeSerial(9, 30, 0) Then _
MsgBox Range("A1").Value
End Sub

BTW if you cross-post, chances are that you don't see some
replies or people post their replies when you already got an
answer, i.e. waste their time.

Regards,
KL



<a wrote in message ...
hi,

case 1
i have this column, with time in US locale and in the format
H:MM , for example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30
becomes 0.39583333, the time serial code.
but its still useless when I write if range(M2).value =
0.39583333 then DO SOMETHING




thnks.

ya, i tried tat,
VBA would give me a "expected: line nubmer or label or statement or end of
statement" compile error.
i guess its bcos, by using double quotes, we break the formula up into 3
parts.


"Chip Pearson" wrote in message
...
Use double quotes instead of apostrophes.

Range("N2").FormulaR1C1 = "=HOUR(RC[-1]" & ":" & MINUTE(RC[-1])"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



<a wrote in message ...
thanks. alot,
pretty new to usenet,,, so is it ok to cross post by attaching the next
group to CC?

one minor question.
how do you include special characters in formula? i am tryign to
achieve something like 9: 30.
with the semi-colon separating hours and mins.

Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])"




"KL" wrote in message
...
Hi,

How about this:

Sub Macro4()
If Range("A1").Value = TimeSerial(9, 30, 0) Then _
MsgBox Range("A1").Value
End Sub

BTW if you cross-post, chances are that you don't see some replies or
people post their replies when you already got an answer, i.e. waste
their time.

Regards,
KL



<a wrote in message ...
hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes
0.39583333, the time serial code.
but its still useless when I write if range(M2).value = 0.39583333
then DO SOMETHING




thnks.

Maybe you want:

Range("N2").FormulaR1C1 = "=HOUR(RC[-1])&" & """:""" & "&MINUTE(RC[-1])"

a wrote:

ya, i tried tat,
VBA would give me a "expected: line nubmer or label or statement or end of
statement" compile error.
i guess its bcos, by using double quotes, we break the formula up into 3
parts.

"Chip Pearson" wrote in message
...
Use double quotes instead of apostrophes.

Range("N2").FormulaR1C1 = "=HOUR(RC[-1]" & ":" & MINUTE(RC[-1])"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



<a wrote in message ...
thanks. alot,
pretty new to usenet,,, so is it ok to cross post by attaching the next
group to CC?

one minor question.
how do you include special characters in formula? i am tryign to
achieve something like 9: 30.
with the semi-colon separating hours and mins.

Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])"




"KL" wrote in message
...
Hi,

How about this:

Sub Macro4()
If Range("A1").Value = TimeSerial(9, 30, 0) Then _
MsgBox Range("A1").Value
End Sub

BTW if you cross-post, chances are that you don't see some replies or
people post their replies when you already got an answer, i.e. waste
their time.

Regards,
KL



<a wrote in message ...
hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes
0.39583333, the time serial code.
but its still useless when I write if range(M2).value = 0.39583333
then DO SOMETHING




thnks.






--

Dave Peterson

Hi,
since time serial number is floating point number,
you should not compare time serial numbers simply.
I recommend to compare strings or integers.

Sub Test()

Range("A1").Value = TimeSerial(1, 40, 0)

'This shows False
MsgBox Range("A1").Value = TimeSerial(1, 40, 0)

'compare strings
MsgBox Format(Range("A1").Value, "hh:nn") = "01:40"

'compare total minute
MsgBox CLng(Range("A1").Value * (24 * 60)) = 1 * 60 + 40

'calculate the difference
If Abs(Range("A1").Value - TimeSerial(1, 40, 0)) < TimeSerial(0, 0,
1) Then
MsgBox "same"
End If

End Sub

--
hth
okaizawa

a wrote:
hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..