MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting as text/string in macro


Posted by Mike Winters on December 13, 2001 11:28 AM

Hello again,
In a macro, I have the line
Rng.Offset(, 3) = Format(.Cells(i, 5), "")
The data that it is inserting into the new worksheet is sometimes something like 13E10 or 13E20 or 13A, etc.. How do I format it so that it does not use the A or E in a mathematical way (IE, exponents with the E).
Thanks for any help you can provide.

Sincerely,

Mike Winters
United States Army


Posted by Gary on December 13, 2001 12:11 PM

Specify the second argument in the Format function which tells VB what format to use.

Change to Rng.Offset(,3)=Format(.Cells(i,5),"0.0")

Should stop the E notation.

Posted by Mike Winters on December 13, 2001 1:09 PM

Nope, that didn't do it.

Posted by Mike Winters on December 13, 2001 1:11 PM

Nope, that didn't do it.

Posted by Gary on December 13, 2001 1:33 PM

Try adding Rng.Offset(,3).NumberFormat="0.0" to your macro.

Posted by Bariloche on December 13, 2001 6:53 PM

Mike,

The symbol for text formatting in VBA is "@" (the "at" sign). Try that in your format code. Should work.


enjoy

Posted by Mike Winters on December 14, 2001 9:25 AM

Thanks guys,
The @ sign works the 13A entries, but the 13E10 and 13E20 entries still show up as 130000000000

Sincerely,

Mike Winters
United States Army

: Try adding Rng.Offset(,3).NumberFormat="0.0" to your macro.

Posted by Bariloche on December 14, 2001 6:18 PM

Mike,

Are you formatting the cell/column before putting data in it? I've encountered the same problem you're having and that (formatting before pasting) solved it.


Bar

Thanks guys, The @ sign works the 13A entries, but the 13E10 and 13E20 entries still show up as 130000000000 : Mike, : The symbol for text formatting in VBA is "@" (the "at" sign). Try that in your format code. Should work. : : enjoy :