VBV: How do I enter percentage formatting (e.g. 0.000%) as a string without Excel converting?

ez08mba

Board Regular
Joined
Nov 1, 2011
Messages
216
Office Version
  1. 2016
Platform
  1. Windows
We have a macro that reviews all the formatting of one spreadsheet against another spreadsheet.

My problem:
If spreadsheet "A" has a percentage formatting of 0.00% and spreadsheet "B" has a percentage formatting of 0.000%, when entering the results in a separate "Results" spreadsheet, Excel converts 0.000% as 0.00%.

What I've tried:
I've simply passing them as string values, but since they are numeric, Excel appears to convert them. I've tried using the IsNumeric function the check and set the formatting for these particular "results" cells as text ("@") formatting, but IsNumeric doesn't recognize the string percentages as numeric.

If I push the results to a text file, they are fine, but the customer is going to want a spread sheet of the resulting comparisons. So I'm not sure how to get the percentage formatting to display as text and not as a number.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Format the cell correctly in results sheet

Code:
[COLOR=#006400]single c[/COLOR][COLOR=#006400]ell to 2 decimal places[/COLOR]
Sheets("Results").Range("A1").NumberFormat = "0.00%"

[COLOR=#006400]whole of column B to 3 decimal places[/COLOR]
Sheets("Results").Columns("B").NumberFormat = "0.000%"

[COLOR=#006400]match other sheet formatting[/COLOR]
Sheets("Results").Range("A1").NumberFormat = Sheets("Source").Range("D55").NumberFormat

[COLOR=#006400]etc...[/COLOR]
 
Last edited:
Upvote 0
Format the cell correctly in results sheet

Code:
[COLOR=#006400]single c[/COLOR][COLOR=#006400]ell to 2 decimal places[/COLOR]
Sheets("Results").Range("A1").NumberFormat = "0.00%"

[COLOR=#006400]whole of column B to 3 decimal places[/COLOR]
Sheets("Results").Columns("B").NumberFormat = "0.000%"

[COLOR=#006400]match other sheet formatting[/COLOR]
Sheets("Results").Range("A1").NumberFormat = Sheets("Source").Range("D55").NumberFormat

[COLOR=#006400]etc...[/COLOR]


I like this idea Yongle. Even though this seems a good approaching for "percentage" formatting, I still have the issue of identifying whether the formatting is a percentage for these cells vs other formatting (e.g. general, text, short date, etc). So how would I do that?

This does not work, even though clicking on the cell(s) show formatting as "Percentage" in the ribbon, the following VBA code returns "Normal" and not "Percent" as the formatting indicates:
Code:
Sheets(1).Range("A1").Style

This also doesn't work, even though the string is numeric (or a percentage):
Code:
IsNumeric("0.000%")

So in order to proceed with this idea, I still need to know what formatting is being applied.
 
Last edited:
Upvote 0
Maybe something like
Code:
If Right(Range("A1").NumberFormat, 1) = "%" Then
   'do something
End If
 
Upvote 0
Maybe something like
Code:
If Right(Range("A1").NumberFormat, 1) = "%" Then
   'do something
End If

Yongle and Fluff!

A combination of your ideas works! Thanks for the help and suggestions!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top