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