在消息框中打印出单元格位置（例如E3）

I am iterating from E2 to E15. I want to show a message box and indicate the cell in it. For example is E3 is empty, I want the error to be Please fill in cell E3. I am using the code below. How shall it be changed?

我正在从E2到E15进行迭代。我想显示一个消息框并指出其中的单元格。例如E3是空的,我希望错误是请填写单元格E3。我正在使用下面的代码。怎么改变?

        Set rRng = Sheet1.Range("E2:E15")
        For Each c In rRng.Cells
            If c.Value = "" Then
                      MsgBox ("Please fill in cell" + c)
                      GoTo end_of_for
            End If
        Next
        end_of_for:

3 个解决方案

#1

Like so, using the address property of a range. If you have only one blank you can avoid a loop using specialcells. Note that the concatenator is & rather than +.

像这样,使用范围的地址属性。如果只有一个空白,则可以避免使用特殊单元循环。请注意,连接符是&而不是+。

Set rRng = Sheet1.Range("E2:E15")
For Each c In rRng.Cells
    If c.Value = "" Then
        MsgBox "Please fill in cell " & c.Address
        Exit For
    End If
Next

#2

use SpecialCells to avoid the loop:

使用SpecialCells来避免循环:

If Application.CountA(Sheet1.Range("E2:E15")) <> Sheet1.Range("E2:E15").Cells.Count Then
    Set rrng = Sheet1.Range("E2:E15").SpecialCells(xlCellTypeBlanks)
    If Not rrng Is Nothing Then MsgBox "Please fill in cell(s): " & rrng.Address(0, 0)
End If

This will return the address of all the blank cells at once.

这将立即返回所有空白单元格的地址。

#3

You can get the adresse in the form "$E$2" by calling the method .Address on the Cells object. By replacing the '$' character, you can get what you want

您可以通过调用Cells对象上的方法.Address以“$ E $ 2”的形式获取地址。通过替换'$'字符,您可以得到您想要的

Set rRng = Range("E2:E15")
For Each c In rRng.Cells
    If c.Value = "" Then
        MsgBox ("Please fill in cell " + Replace(c.Address, "$", ""))
        GoTo end_of_for
    End If
Next
end_of_for:

#1