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

[teachman]

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

 

[Eric W]

Where is your lookup list? Is it a range on a spreadsheet? Or is it a VBA array? One of these should work:

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]

The list is a named range, try:

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