(minor edit)
Need add a closing quotation mark in beginning definition
Set CompareRange = Worksheets("Sheet2").Range("C1:C5"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Joel" wrote:


Set CompareRange = Worksheets("Sheet2").Range("C1:C5)
For Each x In Selection
set c = ComPareRange.find(what:=x,lookin:=xlvalues,lookat: =xlwhole)
if not c is nothing then
x.offset(0,1) = x
end if
Next x


"extrafrate" wrote:

Hi, I would like to modify this macro such that it will allow my to search
one column of text against another and reveal where instances of column A
text occurs within column C. Essentially, I would like to have a VBA-version
of this formula "=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)" (for reference to
what I'm trying to do, please see http://support.microsoft.com/kb/213367). I
tried to simply modify the search range but the macro ran with no results
(which I know is incorrect). Thank you for any help you can give. Note: the
two columns of data I wish to compare are; Column A -- ~10,000 server names,
Column C -- ~35,000 system names, and I want to find out which of those
column A servers are found in the column C list. Make sense?

Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
' Set CompareRange equal to the range to which you will
' compare the selection.
Set CompareRange = Range("C1:C5")
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.
' Set CompareRange = Workbooks("Book2"). _
' Worksheets("Sheet2").Range("C1:C5")
'
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 1) = x
Next y
Next x
End Sub