CODEWITHSUNDEEP
excelvbaif-statement
Saz
Saz

Reputation: 11

If Or statements in VBA

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

'''

Upvotes: 1

Views: 105

Answers (1)

VBasic2008
VBasic2008

Reputation: 55073

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

Upvotes: 3

Related Questions