Problem with decimals/percentages
 

Hi all

ok probably a simple problem, but here goes, basically I have a range
of values which I have pulled in from a separate report, however this
is in the wrong format for my colleague to use due to the formulas.

I'm pretty new to macros and have played around to the stage of
copying a named range to a new sheet but the formatting is the
problem.

The numbers are percentages, however the format needs to be whole
numbers, to 2 decimal places and without the percentage sign (so it
doesn't work using the percentage format)...

examples of the numbers are below:

Property 1 0.02800 - 2.80%
Property 2 0.00550 - 0.55%
Property 3 0.08710 - 8.71%
Property 4 0.02910 - 2.91%
Property 5 0.01740 - 1.74%
Property 6 0.01690 - 1.69%

etc
These numbers will change each and every quarter, so not sure if a
"substitute" formula is the way to go or not, any suggestions
gratefully received!!!

Problem with decimals/percentages
 

If all of these numbers fall within a specific range and no other format is
required, such as dates, times, etc. Then you can manually format the range
as General from the menu bar FormatCellsGeneral. If you have to use code,
then, assuming a value is in cell B1, the code: x = CDbl(Range("B1").Value)
would convert the percentage to a number with a floating decimal point. You
would have to make it a string value by formatting to limit it to two decimal
points:
x = Format(CDbl(Range("B1").Value), "#0.00"
Then you can put the variable x value in a range, like A1 by:

Range("A1") = x

Maybe this will help, if I haven't confused you.

"Stav19" wrote:

Hi all

ok probably a simple problem, but here goes, basically I have a range
of values which I have pulled in from a separate report, however this
is in the wrong format for my colleague to use due to the formulas.

I'm pretty new to macros and have played around to the stage of
copying a named range to a new sheet but the formatting is the
problem.

The numbers are percentages, however the format needs to be whole
numbers, to 2 decimal places and without the percentage sign (so it
doesn't work using the percentage format)...

examples of the numbers are below:

Property 1 0.02800 - 2.80%
Property 2 0.00550 - 0.55%
Property 3 0.08710 - 8.71%
Property 4 0.02910 - 2.91%
Property 5 0.01740 - 1.74%
Property 6 0.01690 - 1.69%

etc
These numbers will change each and every quarter, so not sure if a
"substitute" formula is the way to go or not, any suggestions
gratefully received!!!

Problem with decimals/percentages
 

On Mar 25, 11:16*pm, JLGWhiz
wrote:
If all of these numbers fall within a specific range and no other format is
required, such as dates, times, etc. Then you can manually format the range
as General from the menu bar *FormatCellsGeneral. *If you have to use code,
then, assuming a value is in cell B1, the code: *x = CDbl(Range("B1").Value)
would convert the percentage to a number with a floating decimal point. *You
would have to make it a string value by formatting to limit it to two decimal
points:
x = Format(CDbl(Range("B1").Value), "#0.00"
Then you can put the variable x value in a range, like A1 by:

Range("A1") = x

Maybe this will help, if I haven't confused you.



"Stav19" wrote:
Hi all

ok probably a simple problem, but here goes, basically I have a range
of values which I have pulled in from a separate report, however this
is in the wrong format for my colleague to use due to the formulas.

I'm pretty new to macros and have played around to the stage of
copying a named range to a new sheet but the formatting is the
problem.

The numbers are percentages, however the format needs to be whole
numbers, to 2 decimal places and without the percentage sign (so it
doesn't work using the percentage format)...

examples of the numbers are below:

Property 1 * * *0.02800 - 2.80%
Property 2 * * *0.00550 - 0.55%
Property 3 * * *0.08710 - 8.71%
Property 4 * * *0.02910 - 2.91%
Property 5 * * *0.01740 - 1.74%
Property 6 * * *0.01690 - 1.69%

etc
These numbers will change each and every quarter, so not sure if a
"substitute" formula is the way to go or not, any suggestions
gratefully received!!!- Hide quoted text -

- Show quoted text -

thanks for that, will try that in the workbook, my problem is that I
want to show the middle column as whole numbers to 2dp, I'm thinking
something to do with multiplying by 100...