vba 检查excel单元格是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19325300/
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
Check If excel cell is empty
提问by user2872175
I need vba code for excel. I'm checking whether A1, B7, C9 are empty onclick.
我需要excel的vba代码。我正在检查 A1、B7、C9 是否为空 onclick。
If they are empty (any or all of the variables is/are empty), I need to return:
" is so so so cell is empty click ok to fill it"
如果它们是空的（任何或所有变量都是空的），我需要返回：
“如此所以单元格为空点击确定填充它”
If none of the cells is empty, or if all of them are not empty, or they contain any value, I need to return:
"Do my stuff"
如果没有一个单元格为空，或者所有单元格都不为空，或者它们包含任何值，我需要返回：
“做我的事”
There is a link to a particular workbook, but I wanted to check here also.
有一个特定工作簿的链接，但我也想在这里查看。
回答by tigeravatar
Sub tgr()
Dim CheckCell As Range
For Each CheckCell In Sheets("Sheet1").Range("A1,B7,C9").Cells
If Len(Trim(CheckCell.Value)) = 0 Then
CheckCell.Select
MsgBox "Cell " & CheckCell.Address(0, 0) & " is empty. Click OK and populate it.", , "Missing Information"
Exit Sub
End If
Next CheckCell
'All cells filled, code to Do My Stuff goes here
MsgBox "Do my stuff", , "Continue macro"
End Sub
回答by Dick Kusleika
If you care if the cell is truly empty, you need to use the IsEmpty function on the Value property. This will return false for cells with single apostrophes or functions that return an empty string.
如果您关心单元格是否真的为空，则需要对 Value 属性使用 IsEmpty 函数。对于具有单个撇号的单元格或返回空字符串的函数，这将返回 false。
Public Function CellsAreEmpty(ParamArray aCells() As Variant) As Boolean
Dim vItm As Variant
Dim bReturn As Boolean
bReturn = True
For Each vItm In aCells
bReturn = bReturn And IsEmpty(vItm.Value)
Next vItm
CellsAreEmpty = bReturn
End Function
Sub TestCells()
If CellsAreEmpty(Range("A1"), Range("B7"), Range("C9")) Then
Debug.Print "Do stuff"
Else
Debug.Print " is so so so cell is empty click ok to fill it"
End If
End Sub
回答by Santosh
If you prefer using non vba solution then you can conditional formatting. It wont give a message box but would highlight the cells when they are blank.
如果您更喜欢使用非 vba 解决方案，那么您可以使用条件格式。它不会给出消息框，但会在空白时突出显示单元格。
To use conditional formatting follow the below steps
要使用条件格式，请按照以下步骤操作
- Select the cells
- Under Home Tab > Sytles > Conditional formatting
- Conditional formatting dialog box will appear. Click on New Rule button.
- Select use a formula to determine which cells to format.
- Enter the formula (if cell A1 is selected then A1="")
- Click on Format > Goto Fill and select the color you want.
- 选择单元格
- 在“主页”选项卡>“样式”>“条件格式”下
- 将出现条件格式对话框。单击“新建规则”按钮。
- 选择使用公式来确定要设置格式的单元格。
- 输入公式（如果选择了单元格 A1，则 A1=""）
- 单击“格式”>“转到填充”并选择所需的颜色。
回答by Santosh
Is it what you are looking for ?
这是您要找的吗？
Below code will check for sheet1- Range A1, B7, c9 .
下面的代码将检查 sheet1- Range A1, B7, c9 。
Sub checkEmptyCells()
With ThisWorkbook.Sheets("sheet1")
If (Len(.Range("A1")) = 0) Then
MsgBox "Cell A1 is empty. Click ok to fill"
Exit Sub
ElseIf (Len(.Range("B7")) = 0) Then
MsgBox "Cell B7 is empty. Click ok to fill"
Exit Sub
ElseIf (Len(.Range("C9")) = 0) Then
MsgBox "Cell C9 is empty. Click ok to fill"
Exit Sub
Else
MsgBox "Do my stuff"
Exit Sub
End If
End With
End Sub
