Excel 2003 - VBA - Handling Varible Format

[Craig Brandt]

I have a 5 column table that I convert to a simple one column table, which I
copy and paste in an email.
I do this to simplfy and ensure compatibility.
The columns are: Code, Account #, Value, Name and Account type
I would like this table to appear in even columns so I did the following, to
handle that some of these columns will hold data of differing length (ex:
Value or Name)
Changed the font to Courier (even character spacing)
Calculated the number of spaces needed to create even columns
and concatenated the columns with the spacing into a single column.

The problem that I have, is how it handles numbers when treated as text.
I would like the numbers to appear as "#,##0.00" and I have the number
column formated so that it appears appropriately.
When I ask the Length of the cell ( Len(1,234.00) ) it returns a length of
4. I would have liked an 8.
I tried formating the column as #,##0.00
Format(F5,"#,##0.00")
but then I get a #NAME? in the cell.

Is the a simple way to get this formating to work as I would like?

Craig

 

[Ron Rosenfeld]

I have a 5 column table that I convert to a simple one column table, which I
copy and paste in an email.
I do this to simplfy and ensure compatibility.
The columns are: Code, Account #, Value, Name and Account type
I would like this table to appear in even columns so I did the following, to
handle that some of these columns will hold data of differing length (ex:
Value or Name)
Changed the font to Courier (even character spacing)
Calculated the number of spaces needed to create even columns
and concatenated the columns with the spacing into a single column.

The problem that I have, is how it handles numbers when treated as text.
I would like the numbers to appear as "#,##0.00" and I have the number
column formated so that it appears appropriately.
When I ask the Length of the cell ( Len(1,234.00) ) it returns a length of
4. I would have liked an 8.
I tried formating the column as #,##0.00
Format(F5,"#,##0.00")
but then I get a #NAME? in the cell.

Is the a simple way to get this formating to work as I would like?

Craig

If you are concatenating this line in VBA, as you imply, and your columns are
formatted properly, then use the .text property of the cell.

If you are developing the line within VBA, then the Format method should work.

Format is not a worksheet function; it is a VBA method.
--ron

 

[Dave Peterson]

And the worksheet function you could use is:

=text(f5,"#,##0.00")