Search for Variable in Array

[nniedzielski]

I am running a loop, where i go down a list of part numbers, and i need to check each number against a variable list, and if they are in there, it returns yes, and if not, returns a no.

Sheets("77 surplus").Select
                  
    Dim arr As Variant
                  
        arr = Range("I2:I122")
              
        finalrow = Cells(Rows.Count, 1).End(xlUp).Row
        
            For i = 2 To finalrow
        
            Cells(i, 1).Value = partNumber
            
            '**if partNumber variable exists in the list of numbers in arr then**
                'cells(i,4).value = "Y"
            'else
                'cells(i,4).value = "N"
            'End If
                
            Next i

How can i search that list in the array?

 

[nniedzielski]

i am getting this pop up when i try to run the script

 

[mikerickson]

Try

For i = 2 To finalrow
    '.Cells(i, 1).Value = partNumber: Rem I'm not sure what this line is about
    If IsNumeric(Application.Match(.Cells(i,1).Value, arr,0 )) Then
      .cells(i,4).value = "Y"
    Else
      .Cells(i,4.Value) = "N"
    End If
Next i

Or

With Range("A:A")
    With Range(.Cells(1,1), .Cells(Rows.Count,1).End(xlUp))
        With .Offset(0, 3)
            .FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC1, R2C9:R122C9,0), "Y", "N")
            .Value = .Value
        End With
    End With
End With

 

[Fluff]

That looks more like you're antivirus/security kicking in, rather than a problem with Xl.
Can you edit it's settings?

 

[nniedzielski]

Try

For i = 2 To finalrow
    '.Cells(i, 1).Value = partNumber: Rem I'm not sure what this line is about
    If IsNumeric(Application.Match(.Cells(i,1).Value, arr,0 )) Then
      .cells(i,4).value = "Y"
    Else
      .Cells(i,4.Value) = "N"
    End If
Next i

Or

With Range("A:A")
    With Range(.Cells(1,1), .Cells(Rows.Count,1).End(xlUp))
        With .Offset(0, 3)
            .FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC1, R2C9:R122C9,0), "Y", "N")
            .Value = .Value
        End With
    End With
End With

Option A worked,

thanks,