CODEWITHSUNDEEP
excelvba
Iron Man
Iron Man

Reputation: 849

Getting Percentage value to show in the correct format

I have two worksheets with data from different sources. I need to copy the data to a single worksheet and remove duplicates. To achieve this objective, I need all the data formatted the same on both worksheets. All of this is already coded except with one column of data I am having issues. These columns contain a representation for percentage. In worksheet A, the value is showing as .4386 which equates to 43.86%. I use this code that converts the value without issue:

Worksheets("Verification").Range("F2:F2000").NumberFormat = "0.00%"

In worksheet B, the same data is shown as 43.86, but the above code changes it to 4386.00%. I also tried changing this line to .NumberFormat = "General\%" and this almost works, but returns a value of 44%. What do I need to add to my code to get this to show 43.86% on worksheet B?

Upvotes: 1

Views: 8498

Answers (2)

user1274820
user1274820

Reputation: 8144

Sorry for the slow reply in comments - I will just submit an answer.

Like Ralph said, it's really better to make sure they are the same number.

43.1 and .431 are not the same number.

For Each c In [A1:A10]
    If c.Value < 1 Then
        c.Value = c.Value * 100
    End If
    c.NumberFormat = "0.00\%"
Next c

Results:

Results

Upvotes: 4

Ralph
Ralph

Reputation: 9434

You are stating that .4386 on worksheet A is the same data [...] as 43.86 on worksheet B. So, Excel is correct to convert 43.86 to 4386.00%. Maybe you need a conditional formatting: when the number is smaller or equal to 1 then format it "0.00%" and otherwise format it as "0.00""%""".

Yet, I would assume that you'll be running into problems when comparing the data between the sheets with this solution. Hence, I would divide all numbers on sheet B by a 100 first to really make them comparable.

Note, that just by making numbers "look alike" they are not the same. Example: write in cell A1 the value 1000 and in cell B1 also 1000. Then change the number format for A1 to 0 and the number format for B1 to 0, (or to 0. outside the US). A1 will show 1000 while B1 will show 1. If you ask in cell C1 =A1=B1 you will get a TRUE as the answer.

Upvotes: 1

Related Questions