Matching value in Range("A5") against a list of items

teachman

Active Member
Joined
Aug 31, 2011
Messages
319
Hello,

I have a script with an IF Range("A" & r) value is also in a lookup list AND Range("I" & r) THEN do something. I know I can do a bunch of ORs but that doesn't seem particularly neat.

IF Range("A" & r) is in Lookup list named Type AND Range("I" & r) IS NULL Then DoSomething.

Any ideas?

Thanks,

George Teachman
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,293
Where is your lookup list? Is it a range on a spreadsheet? Or is it a VBA array? One of these should work:

Code:
Sub test1()


    r = 10
    
    arr = Array(2, 4, 6, 8)
    If UBound(Filter(arr, Range("A" & r).Value)) >= 0 And Range("I" & r) = "" Then
        x = 1
    End If
    
    If WorksheetFunction.CountIf(Range("P1:P5"), Range("A" & r).Value) > 0 And Range("I" & r) = "" Then
        x = 1
    End If
    
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
The list is a named range, try:

Code:
Sub Match_Value()
  Dim r As Long, f As Range
  For r = 1 To 10
    Set f = Range("Type").Find(Range("A" & r), , xlValues, xlWhole)
    If Not f Is Nothing And Range("I" & r) = "" Then
      'DoSomething
    End If
  Next
End Sub
 

Forum statistics

Threads
1,089,391
Messages
5,407,970
Members
403,175
Latest member
Zaibass

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top