VBA - Compare a variable to range of cells

[kgkev]

Rather than having 10 'if' statements I would like to compare the value of my variable to a list I can edit on a work sheet.

i have tried...

If varDirectory.Value <> Array(Sheets("PO Filter").Range("A2:A100").Value) Then

and

If varDirectory <> Array(Sheets("PO Filter").Range("A2:A100")) Then

but neither work.

Any help would be appreciated.

Thanks

 

[RoryA]

Use Countif and reverse the logic:

If application.countif(Sheets("PO Filter").Range("A2:A100"), varDirectory) = 0 Then

 

[Fluff]

What is varDirectory?

 

[kgkev]

Thanks Rory, Worked perfectly.

 

[RoryA]

Glad to help.