Excel VBA - add a custom number format

Question

I have a file, generated outside Excel with many percentages. All these percentages have a single decimal place. When imported into excel, Excel adds a second decimal place to the percentages - this seems to be some default format for percentages in Excel. It just adds a "0".

I want to format all double-decimal places percentages to a single decimal point. If I do this manually, by using CTRL+H, Find format/replace format, it goes well. But I cannot do this via VBA/macro, even if I record the macro while doing the replacement manually. The code generated by recording the macro is this:

Application.FindFormat.NumberFormat = "0.00%"
Application.ReplaceFormat.NumberFormat = "0.0%"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

The failure happens at:

Application.ReplaceFormat.NumberFormat = "0.0%"

After a lot of trial and error, I found that the new format needs to be in the Custom format list. But single-decimal place percentages is not there by default. If I manually format a single cell to the desired format, the macro works. Of course, I do not want to do that with every file.

Any input would be much appreciated.

Thanks in advance!

If you set a new custom number to the first cell with VBA (.range("A1").NumberFormat = "0.0%;@"), doesn't that new custom number format become listed for future use with a bulk replacement operation? — user4039065
– user4039065, Commented Jan 14, 2015 at 18:47
@Jeeped yep, this seems to solve it :). What exactly does the ";@" do? I am not familiar with this. — horace_vr
– horace_vr, Commented Jan 14, 2015 at 18:58

user4039065 · Accepted Answer · 2015-01-14 19:51:12Z

7

If you apply the custom number format to a single cell, it will become 'listed' and subsequently available for future use with a bulk replacement operation. I might recommend using something like,

 .range("A1").NumberFormat = "0.0%;@"

That particular format mask can be applied to any cell with a percentage or text. If you had a column header label with a text value and applied that CNF, the ;@ portion forces the text to be displayed literally (without change). In this manner you are open to more possibilities where you put the first CNF. After it has been used once, it will be available for bulk replacement operation(s) on the remainder of the percentage values.

answered Jan 14, 2015 at 19:51

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Syntax Junkie Mar 15 at 23:08

Could you explain this more: "...format mask can be applied to any cell with a percentage or text." I mean, if the cell already had a percentage, why would I need to apply a mask to apply another percentage? Do you mean the format ask can be applied to any cell with a number or numeric text? I also don't understand this, "the ;@ portion forces the text to be displayed literally (without change)." How does the ";@" get "activated"? What forces display of text? Say the text is a zip code. Will that be displayed as a percentage or literally? How will Excel decide?

Gary's Student · Accepted Answer · 2015-01-14 19:14:36Z

1

Consider the simple:

Sub dural()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange
        If r.NumberFormat = "0.00%" Then
            r.NumberFormat = "0.0%"
        End If
    Next r
End Sub

answered Jan 14, 2015 at 19:14

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

horace_vr Over a year ago

This works perfectly too, although about 2 times slower than the answer @Jeeped gave. It seems that looping through each cell in a large range is slower than the bulk find/replace. Thanks a lot!

Collectives™ on Stack Overflow

Excel VBA - add a custom number format

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related