Search an array

[Damo10]

Hi,

I have written some code to add items to an array, what I would like to do now is to check if any of the cell contents in worksheet Codes a variable length range from A1 matches any of the values in the array, if it does then I would like the value of cell B in that row change to "Yes"

here is the code I have so far

Sub test()
    Dim codes
    Dim x As Long
    Dim ws As Worksheet
    Dim ws1 As Worksheet
 
    Set ws = Sheets("Data")
    Set ws1 = Sheets("Codes")
 
    codes = ws.Range(ws.Range("A1"), ws.Range("A" & Rows.Count).End(xlUp))
    For x = LBound(codes) To UBound(codes)
    Next x
End Sub

Any help would be great

Regards Damian

 

[Peter_SSs]

.. I would like the value of cell B in that row change to "Yes"

Does that mean there are (or may be) already values in column B of the Codes sheet?

 

[Damo10]

Hi,

Yes there may already be values in colun B

 

[Peter_SSs]

This doesn't use your array but I think it does what you want. Provide more details if it doesn't meet your needs.

Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CheckValues()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Codes")<br>        .Columns("B").Insert<br>        <SPAN style="color:#00007F">With</SPAN> .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Offset(, 1)<br>            .Formula = "=IF(COUNTIF(Data!A:A,A1),""Yes"",C1)"<br>            .Offset(, 1).Value = .Value<br>            .EntireColumn.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Damo10]

Hi Peter,

Thats great, thanks