Format Text to Percent & Value VBA

[billandrew]

Hello All

trying to format one range of text to a percentage and a different range to a value. Code I am using below

[TABLE="width: 72"]
<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72"] Range("D5:G64,L5:O64").Select[/TD]
[/TR]
[TR]
[TD] Selection.NumberFormat = "0.0%"[/TD]
[/TR]
[TR]
[TD] .Value = .Value[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] Range("H5:K64,P5:S64").Select[/TD]
[/TR]
[TR]
[TD] With Selection[/TD]
[/TR]
[TR]
[TD] Selection.NumberFormat = "General"[/TD]
[/TR]
[TR]
[TD] .Value = .Value[/TD]
[/TR]
</tbody>[/TABLE]

 

[NARAYANK991]

Hi ,

See if this works :

Public Sub temp()
           With Range("D5:G64")
                .NumberFormat = "0.0%"
                .Value = .Value
           End With


           With Range("L5:O64")
                .NumberFormat = "0.0%"
                .Value = .Value
           End With


           With Range("H5:K64")
                .NumberFormat = "General"
                .Value = .Value
           End With
           
           With Range("P5:S64")
                .NumberFormat = "General"
                .Value = .Value
           End With
End Sub

 

[billandrew]

Works perfect, I did change the "General" format to "0.0"

Thanks

 

[billandrew]

I changed the code a bit to the following. This does not work, can anyone explain why?

With Range("D5:G64,L5:O64")
.NumberFormat = "0.0%"
.Value = .Value
End With






With Range("H5:K64,P5:S64")
.NumberFormat = "0.0"
.Value = .Value
End With

 

[billandrew]

Actually the percentage is incorrect. If the value is 27.2039 the result percentage is 272 percent. The result should be 27.2 percent

 

[NARAYANK991]

Hi ,

When you include two ranges in the same Range statement , and then use the line of code :

.Value = .Value

both these ranges acquire the same values , which is why I separated them into two separate statements.

Percentage is when a number is divided by 100 ; thus , if you have a value such as 5% , it means 5 divided by 100 , which is 0.05

Conversely , when you have a value which needs to be converted to a percentage , you need to multiply by 100 ; thus , given your value of 27.2039 , the percentage value would be 2720.39 percent.

If you want a result of 27.2% , the actual value would have to be 0.272

 

[billandrew]

I need the result to be 27.2%. How would I now divide the value by 100

 

[NARAYANK991]

Hi ,

Instead of the line of code :

.Value = .Value

you could have :

.Value = Application.Evaluate("=" & .Address & "/" & 100)

 

[billandrew]

awesome Thank You!!!