测试可变范围内的数据是否存在缺失信息，并通知提交者

Question

我是VBA的新手，并且是在其他人的代码基础上构建的，他比我更熟悉VBA！提前感谢您提供的任何提示和建议。

由于我无法发布图像，我将尝试描述数据集。数据来自用户表单，大部分内容在表范围A14:M34中，问题在a列，数据在B-M列。第一行是用户填充的标题，用于标识被检查的单元。下面的数据由下拉菜单填充，下拉菜单中有空白、"是"one_answers"否"选项，还有几行带有数字或字符串。

我想在不同大小的范围内测试每个单元格是否有未回答的问题，如果有问题，请通知用户，并让他们在提交之前完成数据集。

---

    Sub new_p()
        Static AbortProc As Boolean
        Dim iRow As Long
        Dim LastColumn As Long
        Dim aCol As Long
        Dim ws As Worksheet, WS1 As Worksheet
        Dim InputRange As Range
        Set ws = Worksheets("PreparationData")
        Set WS1 = Worksheets("ColdWeatherPreparation")
        Set InputRange = WS1.Range("B15:M34")
        If AbortProc Then Exit Sub
        'find last column in range
        LastColumn = WS1.Cells(14, 2).End(xlToRight).Column
        'define variable range of columns
        For aCol = 2 To LastColumn
            'check that the circuit row is not blank
            'If Cells(14, aCol) Is Not Nothing Then
                If IsEmpty(InputRange) Then
                Msg = "All fields are not populated. Stop submission to resume editing?"
                Ans = MsgBox(Msg, vbYesNo)
                    'if yes stop process
                    If Ans = vbYes Then
                    AbortProc = True
                    Exit Sub
                    End If
                    'if no run rest of script
                    If Ans = vbNo Then
                    MsgBox "Run without Correcting?"
                    AbortProc = False
                    Exit Sub
                    End If
                End If
            'End If
        Next
'more code here that seems to be working
End Sub

---

你会看到我已经评论了一句我认为多余的话。如果End（xlToRight）生成标题行的最后一个填充列，则它们不是空的，因此无需测试。尽管如此，我保留了我没有使用的代码，直到最后的检查完成，它被证明是完全无用的。过多的注释是为了帮助一大群非VBA工作人员在实现之前遵循并验证我的代码。

因此，LastColumn定义似乎有效，我稍后会再次使用它。当我遍历代码时，它会为我的伪数据集循环正确的次数。我觉得isEmpty是我摔倒的地方。

Answer 1

如果B15:M34中的每个单元格都应该是非空的，那么您可以执行以下操作：

If Application.CountBlank(InputRange)>0 Then
    If Msgbox(Msg, vbYesNo) = vbYes Then
        'rest of your code
    End If
End If

编辑：这将对照相应的标题单元格检查每个数据单元格。

Sub new_p()
    Static AbortProc As Boolean
    Dim iRow As Long
    Dim LastColumn As Long
    Dim aCol As Long
    Dim ws As Worksheet, WS1 As Worksheet
    Dim InputRange As Range, rw As Range
    Dim HeaderRange As Range
    Dim x As Long, Msg As String
    Set ws = Worksheets("PreparationData")
    Set WS1 = Worksheets("ColdWeatherPreparation")
    Set HeaderRange = WS1.Range("B14:M14")
    Set InputRange = WS1.Range("B15:M34")
    'are you sure about this next line?
    'once validation has failed once how does it re-run?
    If AbortProc Then Exit Sub
    For Each rw In InputRange.Rows
        For x = 1 To rw.Cells.Count
            If Len(rw.Cells(x).Value) = 0 And _
               Len(Headerange.Cells(x).Value) > 0 Then
                Msg = "All fields are not populated. Stop submission" & _
                       " to resume editing?"
                If MsgBox(Msg, vbYesNo) = vbYes Then
                    AbortProc = True
                    Exit Sub
                Else
                    MsgBox "Run without Correcting?"
                    AbortProc = False
                    Exit Sub
                End If
            End If
        Next x
    Next rw
    'more code here that seems to be working
End Sub

Answer 2

Len行出现错误？也许，因为Cells有两个参数？CCD_ 1。

此外，您可以通过以下方式设置LastColumn：

LastColumn = ActiveSheet.UsedRange.Columns.Count

行也可以做同样的事情：

LastRow = ActiveSheet.UsedRange.Rows.Count

也许您应该将If AbortProc Then Exit Sub移动到For循环中（作为第一行/最后一行）