The cycle doesn't enter in second if statement in excel

Question

Well, i resolve a little problem abouut one minute ago but now i've another one on my macro :(

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

    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

                        MsgBox "Insert value in the cell " & _
                        .Range("E" & i).Address

                        Cancel = True

                        Exit For
                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

                        MsgBox "Insert value in the cell " & _
                        .Range("F" & i).Address, _
                        .Range("G" & i).Address, _
                        .Range("H" & i).Address

                        Cancel = True

                        Exit For
                End Select
            End If
        Next i
    End With
End Sub

The first if works but the second

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

                        MsgBox "Insert value in the cell " & _
                        .Range("F" & i).Address, _
                        .Range("G" & i).Address, _
                        .Range("H" & i).Address

                        Cancel = True

                        Exit For
                End Select
 End If

Nope. Is there something wrong?

As you can see is the same condition but in different columns

I feel, I wasted my time providing a detailed answer to your [previous question](http://stackoverflow.com/questions/18758663/wrong-cycle-in-macro-control-in-excel) :) — Siddharth Rout, Sep 12 '13 at 08:42
This quest i opened before you answere me :( Your answere was perfect in the other quest. — David_D, Sep 12 '13 at 10:04
I am ready to help you if you apply those suggestions to this code and update your question. — Siddharth Rout, Sep 12 '13 at 10:05
I've edited my quest.. I was wrong before.. I have different columns. But the condition is the same. I don't know what's wrong now. — David_D, Sep 12 '13 at 10:27

Siddharth Rout · Accepted Answer · 2013-09-12T12:58:54.717

If the first IF works then then second will not as we are exiting the FOR Loop.

Is this what you are trying (UNTESTED)?

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

    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
                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
                End Select
            End If

            If sMsg <> "" Then
                MsgBox "One or all these cells are empty. " & _
                       "Please insert value in the cell(s) " & _
                        sMsg
                Cancel = True
                Exit For
            End If
        Next i
    End With
End Sub

FOLLOWUP (from comments)

UNTESTED

This stores the relevant cells in a range and then simply selects it after activating the relevant sheet. However I do not recommend this method. Alternatively you could color the cells... either via code or via conditional formatting...

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

Is there a way to do a focus in the empty cell? I mean, for example, I get the MsgBox `One or all these cells are empty` and the "coordinates" of the cell. When i click `Ok` is there a way to "bring" me in the cell directly? i don't know if you follow me :) — David_D, Sep 12 '13 at 12:43
Yes it is. Though I do not recommend it. Store all the address in a range object and then do a `Rng.Select` — Siddharth Rout, Sep 12 '13 at 12:44
Can you show me? Sorry but it's very first times i use vba. Why you do not recommend it? — David_D, Sep 12 '13 at 12:49
[THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179) is why I do not recommend it. Let me see if I can amend the above code to show how to select the cells... — Siddharth Rout, Sep 12 '13 at 12:53
Thanks it works! So you're saying that the code could be slower? — David_D, Sep 12 '13 at 13:09
Coloring the cells do you think is better? But when i click on the cell the color "disappear" and the cell become transparent again? — David_D, Sep 12 '13 at 14:10
i found this `Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 46` if insert it in `if Not rng Is Nothing Then..` condition, all empty cells become red. The best way would be the only one interessed cell color in red and when i click in turns white..is it possible? — David_D, Sep 12 '13 at 14:21
Hi @Siddharth Rout, Is there a way to indicate which row is empty? In the second condition i can find: `If sMsg = "" Then sMsg = .Range("F" & i).Address & " OR " & _ .Range("G" & i).Address & " OR " & _ .Range("H" & i).Address ..` But if one of this is compiled it shows me the same error. So it seems that also the compiled cell is empty even if it isn't. Are you following me? — David_D, Sep 13 '13 at 09:34

The cycle doesn't enter in second if statement in excel

1 Answers1