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
192
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,717
Office Version
365
Platform
Windows
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:

ez08mba

Board Regular
Joined
Nov 1, 2011
Messages
192
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Maybe something like
Code:
If Right(Range("A1").NumberFormat, 1) = "%" Then
   'do something
End If
 

ez08mba

Board Regular
Joined
Nov 1, 2011
Messages
192
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top