vba NumberFormat 有 0 位小数，但千位分隔符“'”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45733877/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NumberFormat with 0 decimals, but thousands separator " ' "
提问by dv3
I want to have whole numbers like: 14'119'838- it also can just be 0. If I try this:
我想要整数，例如：14'119'838- 它也可以是 0。如果我尝试这个：
Range("C3:H14").NumberFormat = "0"
The Apostrophe-thousand-separator disappears. If I set the NumberFormat to "General", there is no thousands separator.... How can I have 0 decimal places, but with the beautiful thousands separator?
撇号千位分隔符消失。如果我将 NumberFormat 设置为“General”，则没有千位分隔符......我怎么能有 0 个小数位，但是有漂亮的千位分隔符？
Thanks
谢谢
回答by Shai Rado
Change your NumberFormatfrom "0"to:
将您的NumberFormat从更改"0"为：
Range("C3:H14").NumberFormat = "#,##0"
回答by Moosli
You have to change your Number Fromat to this:
您必须将 Number Fromat 更改为：
Range("C3:H14").NumberFormat = "#'###'##0"
if you use this Range("C3:H14").NumberFormat = "#,##0"Formula you will get the English Number format.
Since you ask for the " ' " Seperartor you will need the "#'###'##0"Number Format to get the Swiss Number Format.
如果您使用此Range("C3:H14").NumberFormat = "#,##0"公式，您将获得英文数字格式。由于您要求“'”分隔符，您将需要“#'###'##0”数字格式来获得瑞士数字格式。
Code for changing the NumberFormat by the length of the Number
通过数字的长度更改 NumberFormat 的代码
For Each Rng In Range("a1:B5")
Select Case Len(Rng.Value) 'hier Spalte A
Case Is < 4: Rng.NumberFormat = "##0"
Case 4 To 6: Rng.NumberFormat = "#'##0"
Case 7 To 11: Rng.NumberFormat = "#'###'##0"
End Select
Next
回答by AntiDrondert
Welp, sorry I'm late, here is another solution:
好吧，抱歉我迟到了，这是另一个解决方案：
Option Explicit
Sub again()
Dim rng As Range
set rng = ThisWorkbook.Worksheets("Sheet1").Range("C3:H14")
Application.UseSystemSeparators = False
Application.ThousandsSeparator = "'"
rng.NumberFormat = "#,##0"
End Sub
If you have other number formats you do not want to separate with " ' " it won't do, since it replaces default Excel separator with " ' "
如果您有其他数字格式，您不想用“ ' ”分隔它不会这样做，因为它用“ ' ”替换了默认的Excel分隔符
