CODEWITHSUNDEEP
vbaexcelif-statement
David_D
David_D

Reputation: 1402

How indicate which cell is empty in excel?

I have this macro

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim S1 As String, S2 As String
    Dim S3 As String, S4 As String, sMsg As String
    Dim lRow As Long, i As Long
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    S1 = "Football": S2 = "Basket": S3 = "Sport1": S4 = "Sport2"

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            If Len(Trim(.Range("E" & i).Value)) = 0 Then
                Select Case .Range("C" & i).Value
                    Case S1, S2
                        sMsg = .Range("E" & i).Address
                        Set rng = .Range("E" & i)
                End Select
            End If

            If (Len(Trim(.Range("F" & i).Value)) = 0) Or _
               (Len(Trim(.Range("G" & i).Value)) = 0) Or _
               (Len(Trim(.Range("H" & i).Value)) = 0) Then
                Select Case .Range("C" & i).Value
                    Case S3, S4
                        If sMsg = "" Then
                            sMsg = .Range("F" & i).Address & " OR " & _
                                   .Range("G" & i).Address & " OR " & _
                                   .Range("H" & i).Address
                        Else
                            sMsg = sMsg & " OR " & _
                                   .Range("F" & i).Address & " OR " & _
                                   .Range("G" & i).Address & " OR " & _
                                   .Range("H" & i).Address
                        End If

                        If rng Is Nothing Then
                            Set rng = .Range("F" & i & ":H" & i)
                        Else
                            Set rng = Union(rng, .Range("F" & i & ":H" & i))
                        End If
                End Select
            End If

            If sMsg <> "" Then
                MsgBox "One or all these cells are empty. " & _
                       "Please insert value in the cell(s) " & _
                        sMsg

                If Not rng Is Nothing Then
                    .Activate
                    rng.Select
                End If

                Cancel = True
                Exit For
            End If
        Next i
    End With
End Sub

Right now at this point 

If sMsg = "" Then
   sMsg = .Range("F" & i).Address & " OR " & _
        .Range("G" & i).Address & " OR " & _
        .Range("H" & i).Address
   Else
   sMsg = sMsg & " OR " & _
        .Range("F" & i).Address & " OR " & _
        .Range("G" & i).Address & " OR " & _
        .Range("H" & i).Address
End If

The Macro gets me a MsgBox with an error indicates me "Insert Value in the cell Fi OR Gi OR Hi" Where i is the row. The problem is that if one of this is correctly compiled (for example the F column) the message it's always the same. I need that if only one for example is empty it gets me the error that only that one is incorrect. For example; F and G are correct but H is empty.. The error will be `"Insert Value in the cell Hi". What have i to change?

Upvotes: 1

Views: 532

Answers (1)

user2480047
user2480047

Reputation:

Just create the given string (sMsg) by analysing each specific range (and, logically, replace "OR" with "AND"). Sample code:

Dim curEmpty As String
Dim col1 As String, col2 As String, col3 As String
col1 = "Column F"
col2 = "Column G"
col3 = "Column H"       
If (Len(Trim(.Range("F" & i).Value)) = 0) Then curEmpty = col1

If (Len(Trim(.Range("G" & i).Value)) = 0) Then
   If (curEmpty <> "") Then curEmpty = curEmpty & " AND "
   curEmpty = curEmpty & col2
End If

If (Len(Trim(.Range("H" & i).Value)) = 0) Then
   If (curEmpty <> "") Then curEmpty = curEmpty & " AND "
   curEmpty = curEmpty & col3
End If

If sMsg = "" Then
   sMsg = curEmpty
Else
   sMsg = sMsg & " AND " & curEmpty
End If

Upvotes: 1

Related Questions