find an array value in a column

[FuNeS13]

I have an array in my variable myArray with different part numbers in vba... and I want to loop througgh column A which has part numbers and if any of the part numbers match any of the array values I want to put the text "Found" in the same row in column D... Anyone know how to achieve this?

 

[Fluff]

Anyone know how to achieve this?

Yes.

If you want any more help, then please post your code.

 

[FuNeS13]

I was trying to do it like this but it doesn't work:

Dim myArray As Variant
Dim leLR As Long
Dim i As Long

leLR = Sheets("ZCSS").Cells(Rows.Count, "A").End(xlUp).Row
myArray = Array("216996", "215833", "211873", "214339", "214339", "214407", "217017", "212229", "186089", "211289", "211318", "214310", "214405", "215294", "215767", "216495", "216793", "215292", "215293", "204738", "204746", "207888", "209289", "212346", "215517", "215557", "215988", "216791", "217155", "217437", "216790", "214404", "211968", "215251", "215249", "215291", "211024", "214319", "186516", "214306", "210119", "210120", "210129", "211643", "214360", "214362", "214363", "207823", "214427", "214311", "211754", "210662", "215953", "215303", "214428", "214409", "214323", "181995", "196401", "214314", "214315", "214320", "214338", "216992", "203253", "210121", "214424", "214425", "217475", "217144", "214399", "215235", "215952", "216125", "216856", "217270", "210127", "214952", "215773", "215958", "211646", "217204", "216731", "216856", "215952", "215235", "211646", "214399", "216125", "217270", "217144", "216731")

i = 2

Do Until i = leLR + 1

If Range("A" & i).Value = myArray Then

    Range("D" & i).Value = "Found"

Else
End If


i = i + 1
Loop

 

[Fluff]

Thanks for that, try it like

Dim myArray As Variant, x As Variant
Dim leLR As Long
Dim i As Long

leLR = Sheets("ZCSS").Cells(Rows.Count, "A").End(xlUp).Row
myArray = Array("216996", "215833", "211873", "214339", "214339", "214407", "217017", "212229", "186089", "211289", "211318", "214310", "214405", "215294", "215767", "216495", "216793", "215292", "215293", "204738", "204746", "207888", "209289", "212346", "215517", "215557", "215988", "216791", "217155", "217437", "216790", "214404", "211968", "215251", "215249", "215291", "211024", "214319", "186516", "214306", "210119", "210120", "210129", "211643", "214360", "214362", "214363", "207823", "214427", "214311", "211754", "210662", "215953", "215303", "214428", "214409", "214323", "181995", "196401", "214314", "214315", "214320", "214338", "216992", "203253", "210121", "214424", "214425", "217475", "217144", "214399", "215235", "215952", "216125", "216856", "217270", "210127", "214952", "215773", "215958", "211646", "217204", "216731", "216856", "215952", "215235", "211646", "214399", "216125", "217270", "217144", "216731")

i = 2

Do Until i = leLR + 1

x = Application.Match(Range("A" & i).Value, myArray, 0)

   If Not IsError(x) Then Range("D" & i).Value = "Found"
i = i + 1
Loop

 

[FuNeS13]

Thanks, every row is getting error 2042, even the ones that do match...

 

[Fluff]

If the value in col A is not in the array, then nothing will be put in col D if you are using the code I supplied.

 

[FuNeS13]

I did use the code you supplied, I changed the value of cell A3 to the first value of the array (216996) and I still get the error...

If I save the array as an excel list will the match function work?

 

[Fluff]

Are you saying that col D gets "error 2042" entered into it for every row?

 

[FuNeS13]

No, i'm sorry I wasn't clear... in the vbe editor, when I debug the code and hover over variant x i see the value of it is error 2042, nothing is passed on columnd D...

Sorry for the confusion...

 

[Fluff]

In that case are the values in col A text or numbers?