Excel VBA - 添加自定义数字格式

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

我有一个文件,在Excel之外生成,有很多百分比。所有这些百分比都有一个小数位。导入到Excel时,Excel会在百分比中添加第二个小数位 - 这似乎是Excel中百分比的一些默认格式。它只是添加一个“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:

我想将所有双小数位百分比格式化为一个小数点。如果我手动执行此操作,使用CTRL + H,查找格式/替换格式,它会很顺利。但我无法通过VBA /宏执行此操作,即使我在手动执行替换时记录宏。录制宏生成的代码如下:

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!

提前致谢!

2 个解决方案

#1

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.

该特定格式掩码可应用于具有百分比或文本的任何单元格。如果您有一个带有文本值的列标题标签并应用了该CNF,则; @部分会强制文本显示(不做更改)。通过这种方式,您可以放置第一个CNF的更多可能性。在使用一次后,它将可用于剩余百分比值的批量更换操作。

#2

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

#1