CODEWITHSUNDEEP
excelvba
XYZKLM
XYZKLM

Reputation: 159

VBA, error in the IF logic, how to exit out of it

I have the following worksheets

Raw > Title > Project 1 > Project 2 > Closing

The code skips worksheets "Raw" and "Title" just fine with "if ws is not". It runs on "Project 1" and "Project 2" just fine but somehow the "inner for loop and inner if" runs on "Closing" too.

The vlookup part in the inner loop runs on "Closing" which I aim not to. I want the inner code to just run on "Project 1" and "Project 2".

Please help.

Code:

' some variable declations above...
' some variable declations above...

Set ws_raw = ThisWorkbook.Worksheets("Raw")
Set ws_title = ThisWorkbook.Worksheets("Title")
Set ws_closing = ThisWorkbook.Worksheets("Closing")

With ws_raw
    int_last_row_of_raw = .Cells(.Rows.Count, 1).End(xlUp).Row
    int_last_col_of_raw = 4
    Set rng_raw = .Range("A2", .Cells(int_last_row_of_raw, int_last_col_of_raw))
End With

For Each ws In ThisWorkbook.Worksheets
    If Not ws Is ws_raw _
    Or Not ws Is ws_title _
    Or Not ws Is ws_closing Then

        project_name = ws.Range("B1").Value
        int_last_row_of_ws = 13

        For int_current_row_of_ws = 4 To int_last_row_of_ws
            cell_value = ws.Cells(int_current_row_of_ws, 1).Value
            If cell_value = "Concept Stage" Or cell_value = "Development Stage" Or cell_value = "Implementation Stage" Then
                stage_name = cell_value

                With rng_raw
                    .AutoFilter 1, project_name
                    .AutoFilter 2, stage_name
                End With

                Set rng_filtered_raw = ws_raw.Range("C3", ws_raw.Cells(int_last_row_of_raw, int_last_col_of_raw)).SpecialCells(xlCellTypeVisible)
            Else
                If Not rng_filtered_raw Is Nothing Then
                    MsgBox (project_name & ": " & cell_value)
                    ws.Cells(int_current_row_of_ws, 2).Value = Application.WorksheetFunction.VLookup(cell_value, rng_filtered_raw, 2, False)
                End If
            End If
        Next int_current_row_of_ws
    End If
Next ws

MsgBox ("before first autofilter toggle")
rng_raw.AutoFilter      ' Toggle off AutoFilter

MsgBox ("before second autofilter toggle")
rng_raw.AutoFilter      ' Toggle on AutoFilter with no criteria

Upvotes: 0

Views: 53

Answers (1)

JvdV
JvdV

Reputation: 75870

You have used OR logic. AND logic makes more sense here when you try to say it must be neither of them.

Without looking at the rest of your code you got two options as I see it:

  • If you are not going to expand on your sheets and you only need to run the inner For loop on "Project 1" and "Project 2", simply change to:

    If ws.Name = "Project 1" Or ws.Name = "Project 2" Then

  • If you planning on keep going this way with excluded worksheets, just change OR to AND logic:

    If Not ws Is ws_raw _
And Not ws Is ws_title_page _ 'Notice you made a typo here!
And Not ws Is ws_closing Then

EDIT

1) Why does OR logic not work in your case:

Let's keep this extremely basic: If you have an apple, and you are checking if this is:

  • not the same as a pear (this return True)
  • or not the same as a banana (this return True)
  • or not the same as an apple (this return False, it's the exact same thing)

Now, what OR logic does is: "Is there any True returned out of these three comparisons?" If the answer is yes, then continue with the inner code.

2) Why does AND logic work in your case:

Let's keep this extremely basic: If you have an apple, and you are checking if this is:

  • not the same as a pear (this return True)
  • and not the same as a banana (this return True)
  • and not the same as an apple (this return False, it's the exact same thing)

Now, what AND logic does is: "Are all three comparisons returning True?" If the answer is yes, then continue with the inner code.

Upvotes: 1

Related Questions