使用 Excel VBA 格式化单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9317146/
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
Cell formatting with Excel VBA
提问by user569125
I am trying to add 0.00 if the cell value is not empty and not 0.Below code i am trying but not working:
如果单元格值不为空且不为 0，我正在尝试添加 0.00。下面的代码我正在尝试但不起作用：
For x = 1 To ActiveWorkbook.Sheets.Count Step 1
With ActiveWorkbook.Worksheets(x).Range("A1:Z30")
Set FinColumn = .Find(What:="price", AFter:=.Cells(1, 1), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If FinColumn Is Nothing Then
Else
I = FinColumn.Row + 1
c = FinColumn.Column
**problem with below Do loop,no clues how to do**
**Do Until .Cells(I, c).Value <> "" And .Cells(I, c).Value <> 0
.Cells(I, c).NumberFormat = "0.00"
I = I + 1
Loop**
End If
End With
Next x
Please provide code to meet below conditions: I want the code to work in both prior versions of 2007 also and must meet below conditions:
请提供代码以满足以下条件：我希望代码也能在 2007 的两个早期版本中运行，并且必须满足以下条件：
if the data is like below in excel:
如果excel中的数据如下：
Emp Name Emp id Empsal
Jhon 1
steve 2 2
mike 3 3.5
paul 4 0
gabriel 5 0.00
bush 6 null
if i run the macro ,data should be converted to below format in Empsal column:
如果我运行宏，数据应在 Empsal 列中转换为以下格式：
Emp Name Emp id Empsal
Jhon 1 0
steve 2 2
mike 3 3.50
paul 4 0
gabriel 5 0.00
bush 6 0
Thanks,
谢谢，
Chaitu
柴图
回答by Aprillion
i recommend using custom number format without using VBA, e.g.:
我建议使用自定义数字格式而不使用 VBA，例如：
[=0]0;0.00
or if you want nice big numbers too:
或者如果你也想要漂亮的大数字：
[=0]0;[>=1000]# ### ###;0.00
回答by Siddharth Rout
TRIED AND TESTED (FASTEST METHOD)
久经考验（最快的方法）
This code uses Conditional Formatting. The only drawback is that this code will work in Excel 2007 onwards :P So if you have Excel 2003 then this will not work.
此代码使用条件格式。唯一的缺点是此代码将在 Excel 2007 及以后的版本中工作：P 所以如果您有 Excel 2003 那么这将不起作用。
Option Explicit
Sub Sample()
Dim x As Long
Dim FinColumn As Range
For x = 1 To ActiveWorkbook.Sheets.Count
With ActiveWorkbook.Worksheets(x).Range("A1:Z30")
Set FinColumn = .Find(What:="price", AFter:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FinColumn Is Nothing Then
With Worksheets(x).Columns(FinColumn.Column)
.NumberFormat = "General"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0"
.FormatConditions(1).NumberFormat = "0.00"
.FormatConditions(1).StopIfTrue = True
End With
End If
End With
Next x
End Sub
回答by Skytunnel
Okay, I think I know what you mean now. The problem is that you've entered an impossible Do UntilStatement, because you cell value could never be Blank AND0 at the same time, so your likely getting an infinite loop
好的，我想我现在知道你的意思了。问题是您输入了一个不可能的Do Until语句，因为您的单元格值永远不可能同时为空白AND0，因此您可能会遇到无限循环
Below, I've simply replaced your ANDwith OR
下面，我简单地将你的替换AND为OR
Do Until .Cells(I, c).Value <> "" Or .Cells(I, c).Value <> 0
.Cells(I, c).Value = "0.00"
I = I + 1
Loop
Or, if that still doesn't cover it, then try using <> Empty, because Emptywould cover both 0 and ""
或者，如果仍然没有覆盖它，那么尝试使用<> Empty，因为Empty会覆盖 0 和 ""
Do Until .Cells(I, c).Value <> Empty
.Cells(I, c).Value = "0.00"
I = I + 1
Loop
回答by Skytunnel
I take it you mean you want "0.00"entered as a numeric value but formatted to look like "0.00"? In which case...
我认为你的意思是你想"0.00"输入一个数值但格式看起来像“0.00”？在这种情况下...
.Cells(I, c).Value = 0
.Cells(I, c).NumberFormat = "0.00"
回答by jdh
Your loop:
你的循环：
Do Until .Cells(I, c).Value <> "" And .Cells(I, c).Value <> 0
.Cells(I, c).Value = "0.00"
I = I + 1
Loop
Is looping until it finds a non-null/non-zero value, and it unconditionally sets every null cell to 0 here:
一直循环直到找到一个非空/非零值，它无条件地将每个空单元格设置为 0：
.Cells(I, c).Value = "0.00"
(With general formating on a fresh worksheet, this value would appear as 0, not 0.00, unless you are either resusing a worksheet or changed your default formatting).
（对于新工作表上的常规格式，此值将显示为 0，而不是 0.00，除非您重新使用工作表或更改了默认格式）。
If you meant to only set non-null values, you need to change loop's limit condition doesn't conflict with the secondary non-null/null cell test. Within that loop ( that's search for non-null), do something independently to check each cell for non-null or not. (like a if/then/else to set its value).
如果您打算只设置非空值，则需要更改循环的限制条件与辅助非空/空单元格测试不冲突。在该循环中（即搜索非空值），独立执行某些操作以检查每个单元格是否为非空值。（就像一个 if/then/else 来设置它的值）。
