数据验证例程不起作用

Question

>我有一个宏，它检查从A单元格到I的空白。如果单元格为空白，则用户无法保存。但问题是，如果我不在 A 单元格中输入任何内容，则启用保存。

这是我的 VBA 代码：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rsave As Range, N As Long
    Dim cell As Range
    With Sheet2
        N = Cells(Rows.Count, "A").End(xlUp).Row
        For i = 1 To N
            Set rsave = Range("A" & i & ":I" & i)
            For Each cell In rsave
                If cell = "" Then
                    Dim missdata
                    missdata = MsgBox("missing data", vbOKOnly, "Missing Data")
                    Cancel = True
                    cell.Select
                    Exit Sub
                End If
            Next cell
        Next i
    End With
End Sub

Answer 1

我认为你应该正确地引用你正在处理的对象。
例如，您在此行中使用With Statement，但下一行并未真正引用Sheet2。

With Sheet2
    N = Cells(Rows.Count, "A").End(xlUp).Row

若要正确引用属性和对象，请在其前面加上如下dot：

With Sheet2
    N = .Cells(.Rows.Count, "A").End(xlUp).Row 'notice the dot before Cells

这行也是如此：Set rsave = Range("A" & i & ":I" & i)
它应该是：

Set rsave = .Range("A" & i & ":I" & i) 'notice the dot

现在，您可以使用嵌套循环来执行嵌套循环，而不是执行嵌套循环。
此外，相对于整个列引用最后一行。尝试以下代码：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rsave As Range, N As Long
    Dim cell As Range
    With Sheet2
        N = .Range("A:I").Find("*", [A1], , , xlByRows, xlPrevious).Row
        Set rsave = .Range("A1:I" & N)
        For Each cell In rsave
            If cell = "" Then
                MsgBox "Missing data.", vbOKOnly, "Missing Data"
                Cancel = True
                cell.Select
                Exit Sub
            End If
        Next cell
    End With
End Sub

你看，cell变量从左到右，向上迭代。
所以它检查 A1、B1、C1。等等，然后是 A2、B2、C2...呵呵。