If then VBA (multiple) - better way

[rdw72777]

I know there must be a better way, but i can't quite get the syntax right. I'm sure the array or inlist functions must work but I can't quite figure it out. I want something where I write myTA inlist ("Division A', Division C", etc) so I don't have to have 5-10 "or myTA =...." segments of code....

if MyTA = "Division A" or myTA ="Division c" or myTA="Division D" or myTA = "Division Q" then
        Sheets(myTA).Visible = False
End If

 

[VoG]

Try like this

Dim ws As Worksheet
For Each ws In Worksheets(Array("Division A", "Division c", "Division D", "Division Q"))
        ws.Visible = False
End If

 

[rdw72777]

Vog, thanks, how would i differentiate this even further. Say if myTA is equal to one of the values I gave (Division A, Division c"...etc), how could I hide a sheet named "Sheet1".

(FYI, myTA is defiend as a variant and is a value set by a named range. So while I might want to hide a sheet name myTA, I'd also like to hide some other sheets based on the value of myTA, btu the other sheets won't have the same name as myTA)

 

[ChrisDaniels]

This should work as expected, i use these statements all the time.

If its failing, you need to debug it to see where it is failing. I would guess it would be your var MyTA.

 

[VoG]

I don't think I understand quite what you are asking. Perhaps you mean something like

If IsNumeric(Application.Match(myTA, Array("Division A", "Division c", "Division D", "Division Q"), 0)) Then
        Sheets(myTA).Visible = False
End If

 

[rdw72777]

Exactly what i needed. I jsut wanted to hide a sheet with a name other than myTA

If IsNumeric(Application.Match(myTA, Array("Division A", "Division c", "Division D", "Division Q"), 0)) Then
        Sheets("Div by Region").Visible = False
End If

Thanks,