Hi Howard,

Am Sun, 17 Apr 2016 02:15:54 -0700 (PDT) schrieb L. Howard:

This works but is too slow as you would expect. Fine for the two dozen +/- rows I am testing on. Tried the ole Array caper but far as I could get was writing column C into an array and a msgbox showing how many elements were in the array.

I have old list in column A. New list is in column C.

List items look like this CVT07DR, ASC99YT...

Taking each C list item and find a match for it in A list, when found it goes in column B next to its match in A. Take next item on C list and do the same until all of C list has been processed.

if you don't have duplicates try:

Sub Find_List_cRows()

Dim aRows As Long, cRows As Long, i As Long
Dim aVal As Range
Dim varData As Variant
Dim wsh As Worksheet

Application.ScreenUpdating = False

For Each wsh In Worksheets
With wsh
cRows = .Cells(.Rows.Count, "C").End(xlUp).Row
aRows = .Cells(.Rows.Count, "A").End(xlUp).Row
varData = .Range(.Cells(1, 3), .Cells(cRows, 3))

For i = LBound(varData) To UBound(varData)
Set aVal = .Range("A:A").Find(What:=varData(i, 1), _
after:=.Range("A" & aRows), _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not aVal Is Nothing Then aVal.Offset(, 1) = aVal
Next 'i
.Range("B1:B" & aRows).SpecialCells(xlCellTypeBlanks) =
"missing"
End With

Next 'wsh
Application.ScreenUpdating = True

End Sub

If you have duplicates post here in which column these duplicates can
occure. If they are in Column A you have to use FindNext. If they are in
column C then must be created unique values first.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional