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?

 

[Fluff]

How about

    Dim Dic As Object
    Dim Cl As Range
    
    Sheets("77 surplus").Select
    
    Set Dic = CreateObject("Scripting.dictionary")
    For Each Cl In Range("I2:I122")
         Dic.Item(Cl.Value) = Empty
    Next Cl
    For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Dic.exists(Cl.Value) Then
             Cl.Offset(, 3).Value = "Y"
         Else
             Cl.Offset(, 3).Value = "N"
         End If
     Next Cl

 

[nniedzielski]

Run-time error '429':

ActiveX component cant create object

 

[Fluff]

Are you running this on a Mac?

 

[nniedzielski]

no sir, windows

 

[nniedzielski]

the error message happened on this line:

Set Dic = CreateObject("Scripting.dictionary")

 

[Fluff]

Goto the syswow64 folder C:\Windows\SysWOW64
and check that you have a file called scrrun.dll

 

[nniedzielski]

yes i do

 

[Fluff]

Interesting.
In the VB editor > Tools > References > scroll down & check "Microsoft Scripting Runtime" (make sure you check the box, rather than just select the line)
Does it let you do that?
If so try running the code again. What happens?

 

[nniedzielski]

it let me select it, it still shows as checked, i saved, closed out and went back in, and still got the same error message