VBA - Compare a variable to range of cells

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
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...

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

and

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

but neither work.

Any help would be appreciated.

Thanks
 
Last edited:

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,513
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Use Countif and reverse the logic:

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,426
Office Version
  1. 365
Platform
  1. Windows
What is varDirectory?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,513
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,179
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top