Converting number to text with formatting in VBA

's Computer Specifications

ok, her is what I have
E F
14:00 15:00

I want the out put to look the same only converted to text in the l
column. I can do it with formulas in each cell, but when I try to get
VBA to drop the formulas in the cells, it errors. I am using:

Range("l1:l4000").FormulaR1C1 = _
"=text(RC[-4],"[h]:mm")"

Range("m1:m4000").FormulaR1C1=_
"=text(RC[-4],"[h]:mm")"

basically it is seeing the " and taking that as the end.

Any ideas?

's Computer Specifications

You need to double up the internal double quotes eg

Range("l1:l4000").FormulaR1C1 = _
"=text(RC[-4],""[h]:mm"")"

Hope this helps
Rowan

(E-Mail Removed) wrote:
> ok, her is what I have
> E F
> 14:00 15:00
>
> I want the out put to look the same only converted to text in the l
> column. I can do it with formulas in each cell, but when I try to get
> VBA to drop the formulas in the cells, it errors. I am using:
>
> Range("l1:l4000").FormulaR1C1 = _
> "=text(RC[-4],"[h]:mm")"
>
> Range("m1:m4000").FormulaR1C1=_
> "=text(RC[-4],"[h]:mm")"
>
> basically it is seeing the " and taking that as the end.
>
> Any ideas?
>

's Computer Specifications

your a geniuse thanks

's Computer Specifications

You're welcome.

(E-Mail Removed) wrote:
> your a geniuse thanks
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to prevent a number stored as text from converting to a number	Aaron Rubinstein	Microsoft Excel Programming	2	15th Aug 2009 12:20 AM
Converting a text word or text string to a number	Tom	Microsoft Excel Misc	6	2nd Jan 2009 08:23 PM
Converting numbers and number formatting to a CSV file ?	=?Utf-8?B?Q2VjaWxpYQ==?=	Microsoft Excel Worksheet Functions	1	30th Mar 2007 07:42 PM
Re: macro for converting number stored as "text" (or preceeded with ') to "number" formatting	markx	Microsoft Excel Programming	1	30th Jun 2006 12:14 PM
macro for converting number stored as "text" (or preceeded with ') to "number" formatting	markx	Microsoft Excel Programming	1	23rd Jun 2006 03:15 PM