excel - 我对这个 Excel vba 做错了什么

Question

我正在尝试检查每列是否为空，但它不起作用。我究竟做错了什么？我需要它进行更多计算。如果我能得到一些帮助，那就太好了。我需要对 f 到 n 中的九个列执行此操作，并且之后我需要将该值作为 bool 值，这就是为什么我按照我根据教程制作的方式制作它的原因。我正在尝试收到一条消息，告诉我列是否为空

Sub emptysinder()
Dim cell As Range
Dim fIsEmpty As Boolean
Dim gIsEmpty As Boolean
Dim hIsEmpty As Boolean
Dim iIsEmpty As Boolean
Dim jIsEmpty As Boolean
Dim kIsEmpty As Boolean
Dim lIsEmpty As Boolean
Dim mIsEmpty As Boolean
Dim nIsEmpty As Boolean
fIsEmpty = False
gIsEmpty = False
hIsEmpty = False
iIsEmpty = False
jIsEmpty = False
kIsEmpty = False
lIsEmpty = False
mIsEmpty = False
nIsEmpty = False
For Each cell In Range("F1:F200")
    If IsEmpty(cell) = True Then
        fIsEmpty = True
        Exit For
    End If
Next cell
If fIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells!"
End If
For Each cell In Range("G1:G200")
    If IsEmpty(cell) = True Then
        gIsEmpty = True
        Exit For
    End If
Next cell
If gIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells "
End If
For Each cell In Range("H1:H200")
    If IsEmpty(cell) = True Then
        hIsEmpty = True
        Exit For
    End If
Next cell
If hIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells have values!"
End If
For Each cell In Range("I1:I200")
    If IsEmpty(cell) = True Then
        iIsEmpty = True
        Exit For
    End If
Next cell
If iIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells have values!"
End If
For Each cell In Range("J1:J200")
    If IsEmpty(cell) = True Then
        jIsEmpty = True
        Exit For
    End If
Next cell
If jIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells have values!"
End If
For Each cell In Range("K1:K200")
    If IsEmpty(cell) = True Then
        kIsEmpty = True
        Exit For
    End If
Next cell
If kIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells have values!"
End If
For Each cell In Range("L1:L200")
    If IsEmpty(cell) = True Then
        lIsEmpty = True
        Exit For
    End If
Next cell
If lIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells have values!"
End If
For Each cell In Range("M1:M200")
    If IsEmpty(cell) = True Then
        mIsEmpty = True
        Exit For
    End If
Next cell
If mIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells have values!"
End If
For Each cell In Range("N1:N200")
    If IsEmpty(cell) = True Then
        nIsEmpty = True
        Exit For
    End If
Next cell
If nIsEmpty = True Then
    MsgBox "All cells are empty in your range!"
Else
    MsgBox "Cells have values!"
End If
End Sub

Answer 1

本质上，您正在检查第 1 行到第 200 行的九列是否为空。使用循环，您可以调用一些自定义函数(见下文)进行检查。

Option Explicit

Public Sub emptysinder()

    Dim i As Long
    Dim r As Range
    
    For i = 1 To 9
        Set r = Range("F1").Offset(0, i - 1).Resize(200, 1)
        If IsAllEmpty(r) Then
            Debug.Print "Range " & r.Address & " is all empty."
        ElseIf IsAnyEmpty(r) Then
            Debug.Print "Range " & r.Address & " is partially empty."
        Else
            Debug.Print "Range " & r.Address & " filled."
        End If
    Next i
    

End Sub

以及即时窗口上的示例输出

Range $F$1:$F$200 is all empty.
Range $G$1:$G$200 filled.
Range $H$1:$H$200 is all empty.
Range $I$1:$I$200 is partially empty.
Range $J$1:$J$200 is all empty.
Range $K$1:$K$200 is partially empty.
Range $L$1:$L$200 is all empty.
Range $M$1:$M$200 is all empty.
Range $N$1:$N$200 is all empty.

在模块中使用以下辅助函数

Public Function IsAllEmpty(ByVal r_range As Range) As Boolean
    Dim Item As Range
    For Each Item In r_range,Cells
        If Not IsEmpty(Item) Then
            IsAllEmpty = False
            Exit Function
        End If
    Next
    IsAllEmpty = True
End Function

Public Function IsAnyEmpty(ByVal r_range As Range) As Boolean
    Dim Item As Range
    For Each Item In r_range.Cells
        If IsEmpty(Item) Then
            IsAnyEmpty = True
            Exit Function
        End If
    Next
    IsAnyEmpty = False
End Function