Curious operation of Paste
I have a spreadsheet with a column of numbers. The numbers actually represent a
percentage, but they're stored as a number (eg, 5.6, not .056). Rather than divide all the numbers by 100, I decided to get cute and applied a custom format of #,##0.0"%". Visually, it looked great. In my macro, I filter the data, copy the visible cells and paste to a new spreadsheet, planning on dividing by 100 every time I used data from this column. Guess what happens? Paste actually changes the data to a real percentage. 5.6 from the original cell is changed to .056, with a format of #,##0.0%! More of the story: don't use custom formats to display percentages. Store your data properly and use a percentage format. -- Regards, Fred |
Curious operation of Paste
I just tried this in xl2003.
The value pasted fine (no change)--both manually and via code. But the format was pasted, too--so it still looked like a percentage. You may want to try paste|special|values to see if that helps you. Fred Smith wrote: I have a spreadsheet with a column of numbers. The numbers actually represent a percentage, but they're stored as a number (eg, 5.6, not .056). Rather than divide all the numbers by 100, I decided to get cute and applied a custom format of #,##0.0"%". Visually, it looked great. In my macro, I filter the data, copy the visible cells and paste to a new spreadsheet, planning on dividing by 100 every time I used data from this column. Guess what happens? Paste actually changes the data to a real percentage. 5.6 from the original cell is changed to .056, with a format of #,##0.0%! More of the story: don't use custom formats to display percentages. Store your data properly and use a percentage format. -- Regards, Fred -- Dave Peterson |
All times are GMT +1. The time now is 12:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com