how to include special characters in a formula?

Hi,

I have tried the suggestion below, but it still doesnt work.
-------------------------------------------------------------------------

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" <chip@cpearson.com> wrote in message
news:uffx2rVhFHA.576@tk2msftngp13.phx.gbl...
> 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
>
>

>> 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])"
>>

[Tom Ogilvy]

Here is a corrected version:

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


--
Regards,
Tom Ogilvy

<a> wrote in message news:42d14b56$1@news.starhub.net.sg...
> Hi,
>
> I have tried the suggestion below, but it still doesnt work.
> -------------------------------------------------------------------------
>
> 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" <chip@cpearson.com> wrote in message
> news:uffx2rVhFHA.576@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
> >> 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])"
> >>
>

[Dave Peterson]

See one more reply at your other thread.

a wrote:
>
> Hi,
>
> I have tried the suggestion below, but it still doesnt work.
> -------------------------------------------------------------------------
>
> 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" <chip@cpearson.com> wrote in message
> news:uffx2rVhFHA.576@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
> >> 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])"
> >>

--

Dave Peterson

thanks alot,
now i realize you dont need & to link up the strings inside a formula.


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42D14F1F.2D9BE07A@verizonXSPAM.net...
> See one more reply at your other thread.
>
> a wrote:
>>
>> Hi,
>>
>> I have tried the suggestion below, but it still doesnt work.
>> -------------------------------------------------------------------------
>>
>> 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" <chip@cpearson.com> wrote in message
>> news:uffx2rVhFHA.576@tk2msftngp13.phx.gbl...
>> > 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
>> >
>> >
>>
>> >> 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])"
>> >>
>
> --
>
> Dave Peterson