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

[ez08mba]

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]

Format the cell correctly in results sheet

[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]

 

[ez08mba]

Format the cell correctly in results sheet

[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:

Sheets(1).Range("A1").Style

This also doesn't work, even though the string is numeric (or a percentage):

IsNumeric("0.000%")

So in order to proceed with this idea, I still need to know what formatting is being applied.

 

[Fluff]

Maybe something like

If Right(Range("A1").NumberFormat, 1) = "%" Then
   'do something
End If

 

[ez08mba]

Maybe something like

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]

Glad we could help & thanks for the feedback