PDA

View Full Version : VBA code finding duplicates in two Excel column.



junior6202
06-17-2014, 10:15 AM
Hello Guys,

Im trying to find matching duplicates in two columns from two different work books. The code that I have so far every time I run it I get an error "Error #9 run time error, Subscript out of range". here is my code. Any help is greatly appreciated.


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("F2:F30642") ' <--- workbook I'm currently using
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.

Set CompareRange = Workbooks("20130613_MasterServicerList_CSS_09272011.xlsx"). _ '<----The line that cause the error.
Worksheets("MASTER LIST").Range("E2:E7220") '<--- Second workbook path and worksheet.

' 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

p45cal
06-17-2014, 11:35 AM
One or more of the following:
the workbook's name is not 20130613_MasterServicerList_CSS_09272011.xlsx (it has to be exactly right (and the workbook has to be open)).
the sheet name is not exactly MASTER LIST (look out for extra spaces in the sheet name, especially at the end or beginning).

ranman256
06-17-2014, 01:42 PM
I dont see anything in SELECTION.