Compare 2 lists

[FredM400]

I'm sure there is a ready answer for this, I just cannot find it.

I have two lists of about 4,000 item each. Each list is on its own worksheet, in range("A1:A4000")

Most of the items will be common to both lists. There will be some things unique to list one and some things unique to list two

In VBA i want to do much like the Exel vLookUp function.
I want to take a value in list one. If the exact same value is in list two, I want to find the row number it occurs on.

Can someone point me in the right direction?

 

[Peter_SSs]

You haven't really said if you want to check every value or what you want to do with the row number if found but this might give you something to work from. Make sure the sheet names in the code match yours and note that I have restricted this to look for just 4 values (rather than work through 4,000 values) to see if is what you want.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Check_In_List()<br>    <SPAN style="color:#00007F">Dim</SPAN> Rng1 <SPAN style="color:#00007F">As</SPAN> Range, Rng2 <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        <SPAN style="color:#00007F">Set</SPAN> Rng1 = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))<br>    End <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        <SPAN style="color:#00007F">Set</SPAN> Rng2 = .Range("A1", .Range("A" & .Rows.Count).<SPAN style="color:#00007F">End</SPAN>(xlUp))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#007F00">'    For Each c In Rng1</SPAN><br>    For Each c In Rng1.Resize(4) <SPAN style="color:#007F00">'**</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> Found = Rng2.Find(What:=c.Value, LookIn:=xlValues, _<br>            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox c.Value & " not found"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox c.Value & " found in row " & Found.Row<br>        End <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[testing lotto]

I have a similar problem. I have 2 large worksheets, several columns and rows and I want to highlight (change cell color not font) the cells on sheet 1 that match any of the cells from sheet 2.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
<o></o>
Sheet 1 Sheet 2
<o></o>
Fred 201<o></o>
100 Fred
723 333
333 Tom<o></o>
George 616