CODEWITHSUNDEEP
excelvbaif-statement
Emibigpacem
Emibigpacem

Reputation: 11

If Else with multiple conditions

I'm trying to implement three conditions and four outcomes:

enter image description here

The values in Col I are driven by a data validation list in the master worksheet.

I tried the following:

If DataImport.Cells(i, "E").Value <> 0 And DataImport.Cells(i, "F").Value <> 0 And DataImport.Cells(i, "I").Value = "Hide" Then
    ThisWorkbook.Worksheets(ws(index)).Visible = False
ElseIf DataImport.Cells(i, "E").Value = 0 And DataImport.Cells(i, "F").Value = 0 And DataImport.Cells(i, "I").Value = "Hide" Then
    ThisWorkbook.Worksheets(ws(index)).Visible = False
ElseIf DataImport.Cells(i, "E").Value <> 0 And DataImport.Cells(i, "F").Value <> 0 And DataImport.Cells(i, "I").Value = "Unhide" Then
    ThisWorkbook.Worksheets(ws(index)).Visible = True
ElseIf DataImport.Cells(i, "E").Value = 0 And DataImport.Cells(i, "F").Value = 0 And DataImport.Cells(i, "I").Value = "Unhide" Then
    ThisWorkbook.Worksheets(ws(index)).Visible = False

The procedure is ignoring my third condition and is still showing related worksheets with "Hidden" in Column I.

Upvotes: 1

Views: 81

Answers (2)

PuravTheGreat
PuravTheGreat

Reputation: 136

Try nesting the if statements to see if that fixes the issue. Additionally, your logic can be simplified because if value in Column E and F is 0, then we always hide the sheet, no matter what the value in Column I is:

If DataImport.Cells(i, "E").Value <> 0 And DataImport.Cells(i, "F").Value <> 0 Then
    If DataImport.Cells(i, "I").Value = "Hide" Then
        ThisWorkbook.Worksheets(ws(index)).Visible = False

    ElseIf DataImport.Cells(i, "I").Value = "Unhide" Then
        ThisWorkbook.Worksheets(ws(index)).Visible = True

    End If

ElseIf DataImport.Cells(i, "E").Value = 0 And DataImport.Cells(i, "F").Value = 0 Then

    ThisWorkbook.Worksheets(ws(index)).Visible = False

End If

Also we can further simplify the code using With:

With DataImport

If .Cells(i, "E").Value <> 0 And .Cells(i, "F").Value <> 0 Then
    If .Cells(i, "I").Value = "Hide" Then
        ThisWorkbook.Worksheets(ws(index)).Visible = False

    ElseIf .Cells(i, "I").Value = "Unhide" Then
        ThisWorkbook.Worksheets(ws(index)).Visible = True

    End If

ElseIf .Cells(i, "E").Value = 0 And .Cells(i, "F").Value = 0 Then

    ThisWorkbook.Worksheets(ws(index)).Visible = False

End If

End With

Upvotes: 0

AJD
AJD

Reputation: 2438

Keep it simple. When I am working with complex logic, I set up variables first so my code is easier to read.

Option Explicit
Dim isNotZero as Boolean
Dim isToHide as Boolean
isNotZero = DataImport.Cells(i, "E").Value <> 0 And DataImport.Cells(i, "F").Value <> 0
isToHide = DataImport.Cells(i, "I").Value = "Hide"

Now, we can use these variables in your code (remember to indent properly so code logic is visible)

If isNotZero And isToHide Then
    ThisWorkbook.Worksheets(ws(index)).Visible = False
ElseIf DataImport.Cells(i, "E").Value = 0 And DataImport.Cells(i, "F").Value = 0 And isToHide Then
    ThisWorkbook.Worksheets(ws(index)).Visible = False
ElseIf isNotZero And Not isToHide Then
    ThisWorkbook.Worksheets(ws(index)).Visible = True
ElseIf DataImport.Cells(i, "E").Value = 0 And DataImport.Cells(i, "F").Value = 0 And Not isToHide Then
    ThisWorkbook.Worksheets(ws(index)).Visible = False
End iF

You notice how I have left some of the code. (A <> 0 AND B <> 0) is not the opposite of (A = 0 AND B = 0). What you were looking for there was (A = 0 OR B = 0). A simple truth table will prove this, and I am going to leave this as an exercise for yourself.

But wait.

Your logic/truth table above was a good thing to do. It shows the pattern of A and C (A and B are the values in E and F being non-zero). It also shows a simple pattern.

ThisWorkbook.Worksheets(ws(index)).Visible = isNotZero and Not isToHide

As you can see, I have replaced your complex If … Then... with a single line of code. Let us make one last tweak to isToHide, and your final code is below.

Option Explicit
Dim isNotZero as Boolean
Dim isToHide as Boolean
isNotZero = DataImport.Cells(i, "E").Value <> 0 And DataImport.Cells(i, "F").Value <> 0
isToExpose = DataImport.Cells(i, "I").Value = "Unhide"
ThisWorkbook.Worksheets(ws(index)).Visible = isNotZero and isToExpose
' Purely because I prefer the positive statements
' And now your code is easier to read and self-commenting.

Lessons here

  • Do the truth tables.
  • Identify the patterns.
  • Simplify seemingly complex object code with Boolean variables.
  • Take it one logical step at time.

Upvotes: 4

Related Questions