vba - 尝试获取单元格 AI 和 BM 的值时出现错误 1004

Question

我正在尝试查找是否在特定单元格中填充了一些强制性值。但是当我试图找到单元格 AI 和 BM 的值时，excel 会抛出错误 1004，但它适用于单元格 X 和 Y。知道如何纠正这个问题吗？

第 1 行有我的标题。

Private Sub CommandButton1_Click()

    Dim value As Range, sheetRange As Range
    Dim j As Integer
    Dim lRow As Integer

    Set sheetRange = Sheet4.Range("A2:BM65536")

    On Error Resume Next
    Set value = Intersect(sheetRange.EntireRow.SpecialCells(xlConstants), sheetRange)

    On Error GoTo 0

    If value Is Nothing Then
        MsgBox "Good to go"
        Exit Sub

    Else
        lRow = Cells(Rows.Count, 1).End(xlUp).Row

        For j = 2 To lRow

            If Len(Sheet4.Range("X" & j).value) = 0 Then
                MsgBox "Enter value for: Data sheet" & vbNewLine & "Column name: " & Range("X1").value & vbNewLine & "Cell X" & j, vbExclamation
                Exit Sub
            End If

            If Len(Sheet4.Range("Y" & j).value) = 0 Then
                MsgBox "Enter value for: Data sheet" & vbNewLine & "Column name: " & Range("Y1").value & vbNewLine & "Cell Y" & j, vbExclamation
                Exit Sub
            End If

            If Len(Sheet4.Range("AB" & j).value) = 0 Then
                MsgBox "Enter value for: Data sheet" & vbNewLine & "Column name: " & Range("AB1").value & vbNewLine & "Cell AB" & j, vbExclamation
                Exit Sub
            End If

            If Len(Sheet4.Range("AI" & j).value) = 0 Then
                MsgBox "Enter value for: Data sheet" & vbNewLine & "Column name: " & Range("AI").value & vbNewLine & "Cell AI" & j, vbExclamation
                Exit Sub
            End If

            If Len(Sheet4.Range("BM" & j).value) = 0 Then
                MsgBox "Enter value for: Data sheet" & vbNewLine & "Column name: " & Range("BM").value & vbNewLine & "Cell BM" & j, vbExclamation
                Exit Sub
            End If

        Next

  End If

    End Sub

Answer 1

问题是在您的 MsgBox AI 行和 BM & Range("AI").value &返回一个值数组，因为 AI是 整栏 .

您可能打算使用 & Range("AI1").value &仅返回标题名称。
& Range("BM").value & 相同.