Excel VBA-添加自定义数字格式 [英] Excel VBA - add a custom number format

问题描述

我有一个在Excel外部生成的文件，包含很多百分比.所有这些百分比都有一个小数位.当导入到excel中时，Excel会将百分比添加到小数点后第二位-这似乎是Excel中百分比的默认格式.它只是添加了一个"0".

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

我想将所有双小数位百分比格式化为单个小数点.如果我手动执行此操作，则使用CTRL + H，查找格式/替换格式，效果很好.但是，即使我在手动进行替换时记录了宏，也无法通过VBA/宏来执行此操作.通过记录宏生成的代码是这样的:

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

失败发生在:

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.

任何输入将不胜感激.

提前谢谢！

推荐答案

如果将自定义数字格式应用于单个单元格，它将被列出"，并随后可用于批量替换操作.我可能会建议使用类似的东西，

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%;@"

该特定格式的掩码可以应用于带有百分比或文本的任何单元格.如果您具有带有文本值的列标题标签并应用了CNF，则; @ 部分将强制按原样显示文本(不更改).通过这种方式，您可以在第一个CNF中 where 处获得更多的可能性.使用一次后，其余百分比值将可用于批量替换操作.

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.

这篇关于Excel VBA-添加自定义数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！