Thanks Claus.

There isn't an easier way to do this is there...?

On Tuesday, November 25, 2014 2:27:13 PM UTC, Claus Busch wrote:
hi,

Am Tue, 25 Nov 2014 05:45:40 -0800 (PST) schrieb
:

In Column C of Sheet 1, I have a list of unique codes (consisting of letters and numbers)
I'd like to search for each code in Sheet 2, Columns B to P; where there is a match, I'd like this to return the result in Column A.

try:

Sub Test()
Dim LRow As Long, LRow2 As Long, i As Long
Dim varCheck As Variant
Dim myStr As String, FirstAddress As String
Dim rngSearch As Range, c As Range

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, "C").End(xlUp).Row
varCheck = .Range("C1:C" & LRow)
LRow2 = Sheets("Sheet2").UsedRange.Rows.Count
For i = LBound(varCheck) To UBound(varCheck)
myStr = ""
Set c = Sheets("Sheet2").Range("B1:P" & LRow2) _
.Find(varCheck(i, 1), LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
myStr = myStr & c.Address(0, 0) & ", "
Set c = Sheets("Sheet2").Range("B1:P" & LRow2).FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
If Len(myStr) 0 Then
.Cells(i, 1) = Left(myStr, Len(myStr) - 2)
End If
Next
End With
End Sub


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