PDA

View Full Version : Set field lengths



zennon
03-14-2007, 08:38 AM
Need help on something that I have never seen done automatically before. I need to populate cells with numeric data from another sheet (This I can do), what I need help with is:

Cell value will be a currency amount e.g. 241.65 I need to make this field a certain length by adding zeros to the amount to make the length 15 characters long with a minus sign at the end for the 15th character. e.g. 00000000241.65-

The thing I need to find out is that all values can be diferent lengths e.g. 1254.25, 10245.85. I need to run a check to see the length and add the appropriate number of zeros to make the 15 characters.

I need to do this with other cells but these will be alpha characters.

HELP!!!!:help

mdmackillop
03-14-2007, 08:52 AM
Look at using custom formatting. Is it to be a "genuine" negative value or just look like one?
Something like
0000000000000.00"-"

or

00;0000000000000.00-;00

zennon
03-14-2007, 09:30 AM
The final figure will be a negative. Can using the custom formating check to see what the length of the value is?

I have a whole sheet where the values will be "Filled", with zeros. Eventually the sheet will be saved as a txt file for an upload to an FTP site for installation to an AS/400 database.

mdmackillop
03-14-2007, 09:37 AM
Did you try it?
If the data are numbers, the cf inserts the number of zeros required, irrespective of length.

zennon
03-15-2007, 10:16 AM
Yes, Cell Formatting worked. Is there a way to do this in VB?

mdmackillop
03-15-2007, 10:22 AM
Try recording a macro to set the CF that you require. That should give you the basic VBA code. I don't know how you intend to apply it.