If Or statements in VBA

Question

I created an entry form that transposes data into a table.
I would like to check that all required cells are filled out before allowing the user to save the data.
I tried using a Range("A2:E2") but it didn't seem to work, so I entered every cell that I want to check.
I am having an issue where my IsEmpty = True Or _ statement seems to never be satisfied. Even if I enter data into every cell I am checking, the MsgBox is displayed, and the Else command is skipped.
After the Else is my Macro for saving the data in the table.
Any help appreciated. Thanks.

If IsEmpty(Range("A2").Value) = True Or _
IsEmpty(Range("B3").Value) = True Or _
IsEmpty(Range("C3").Value) = True Or _
IsEmpty(Range("D3").Value) = True Or _
IsEmpty(Range("E3").Value) = True Or _
IsEmpty(Range("C5").Value) = True Or IsEmpty(Range("C6").Value) = True Or IsEmpty(Range("C7").Value) = True Or IsEmpty(Range("C8").Value) = True Or IsEmpty(Range("C9").Value) = True Or IsEmpty(Range("C10").Value) = True Or IsEmpty(Range("C11").Value) = True Or _
IsEmpty(Range("E5").Value) = True Or IsEmpty(Range("E6").Value) = True Or IsEmpty(Range("E7").Value) = True Or IsEmpty(Range("E8").Value) = True Or IsEmpty(Range("E9").Value) = True Or IsEmpty(Range("E10").Value) = True Or IsEmpty(Range("E11").Value) = True Or _
IsEmpty(Range("G5").Value) = True Or IsEmpty(Range("G6").Value) = True Or IsEmpty(Range("G7").Value) = True Or IsEmpty(Range("G8").Value) = True Or IsEmpty(Range("G9").Value) = True Or IsEmpty(Range("G10").Value) = True Or IsEmpty(Range("G11").Value) = True Or _
IsEmpty(Range("H5").Value) = True Or IsEmpty(Range("H6").Value) = True Or IsEmpty(Range("H7").Value) = True Or IsEmpty(Range("H8").Value) = True Or IsEmpty(Range("H9").Value) = True Or IsEmpty(Range("H10").Value) = True Or IsEmpty(Range("H11").Value) = True Or _
IsEmpty(Range("I5").Value) = True Or IsEmpty(Range("I6").Value) = True Or IsEmpty(Range("I7").Value) = True Or IsEmpty(Range("I8").Value) = True Or IsEmpty(Range("I9").Value) = True Or IsEmpty(Range("I10").Value) = True Or IsEmpty(Range("I11").Value) = True Or _
IsEmpty(Range("J5").Value) = True Or IsEmpty(Range("J6").Value) = True Or IsEmpty(Range("J7").Value) = True Or IsEmpty(Range("J8").Value) = True Or IsEmpty(Range("J9").Value) = True Or IsEmpty(Range("J10").Value) = True Or IsEmpty(Range("J11").Value) = True Or _
IsEmpty(Range("K5").Value) = True Or IsEmpty(Range("K6").Value) = True Or IsEmpty(Range("K7").Value) = True Or IsEmpty(Range("K8").Value) = True Or IsEmpty(Range("K9").Value) = True Or IsEmpty(Range("K10").Value) = True Or IsEmpty(Range("K11").Value) = True Or _
IsEmpty(Range("L5").Value) = True Or IsEmpty(Range("L6").Value) = True Or IsEmpty(Range("L7").Value) = True Or IsEmpty(Range("L8").Value) = True Or IsEmpty(Range("L9").Value) = True Or IsEmpty(Range("L10").Value) = True Or _
IsEmpty(Range("L11").Value) = True _
Then

MsgBox "Please fill out all required cells before saving!"

Else

'''

VBasic2008 · Accepted Answer · 2022-03-21 02:58:50Z

3

Count Blank Cells

Returns a message box if one of the cells in the range is empty, in the first example, or blank, in the second example. Note that the only difference between the solutions is in a different parameter for the LookIn argument of the Find method: xlFormulas (empty) vs xlValues (blank).
I abandoned the Application.CountBlank idea because it doesn't work with non-contiguous ranges.

Empty Cells

Sub FindEmpty()
    
    Dim rgAddress As String: rgAddress = "A2,B3:E3,C5:C11,E5:E11,G5:L11"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim fCell As Range: Set fCell = ws.Range(rgAddress).Find("", , xlFormulas)
    
    If Not fCell Is Nothing Then
        MsgBox "Please fill out all required cells before saving!"
    Else
        ' Continue to save...
        
    End If

End Sub

Blank Cells

Sub FindBlank()
    ' Blank cells are:
    ' 1. empty cells,
    ' 2. cells containing the formula '=""'
    ' 3. cells containing a single quote (')...
    
    Dim rgAddress As String: rgAddress = "A2,B3:E3,C5:C11,E5:E11,G5:L11"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim fCell As Range: Set fCell = ws.Range(rgAddress).Find("", , xlValues)
    
    If Not fCell Is Nothing Then
        MsgBox "Please fill out all required cells before saving!"
    Else
        ' Continue to save...
        
    End If

End Sub

Bonus (if someone wants to play)

Sub PopulateRange()

    Const rgAddress As String = "A2,B3:E3,C5:C11,E5:E11,G5:L11"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

    Dim cell As Range
    Dim n As Long

    For Each cell In ws.Range(rgAddress).Cells
        n = n + 1
        cell.Value = n
    Next cell

End Sub

edited Mar 21, 2022 at 2:58

answered Mar 21, 2022 at 2:33

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Saz Over a year ago

Thank you! I realize I did make a mistake with the cells and once I fixed the ones I specified in my original inefficient long winded code, both methods work. Yours is much more elegant I appreciate your effort! If I wanted to check also if the cells H5:L11 are Integers as well as not being blank, would you suggest I add an Else If and follow a similar method and do something like: Else If Not Fcell Is Int Then

Collectives™ on Stack Overflow

If Or statements in VBA

1 Answer 1

Count Blank Cells

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Count Blank Cells

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related