I have 2 spreadsheets. Each spreadsheet has a series of serial numbers (or other kind of data) listed from A2:A900. I am trying to use the following code to tell me when there is similar data contained in A2:A900 of "both" spreadsheets. The problem is, this code misses a lot of data. Please tell me how i can correct it;
Sub DuplicatesCheck()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range([A2], [A65536].End(xlUp)).Offset(0, 1).Formula = "=IF(COUNTIF(Sheet2!RC[-1]:Sheet2!R[598]C[-1],RC[-1])<>0,""Yes"",""No"")"
Dim theCol As Range, cell As Range, RtoSel As Range
Dim LtoSel As String
Set theCol = Range(Range("B2"), Range("B65536").End(xlUp))
LtoSel = "Yes"
For Each cell In theCol
If Right(cell, 3) = LtoSel Then
If RtoSel Is Nothing Then
Set RtoSel = cell
Else
Set RtoSel = Application.Union(RtoSel, cell)
End If
End If
Next
On Error GoTo e
RtoSel.Offset(0, -1).Select
With Selection
.Font.FontStyle = "Bold"
.Interior.ColorIndex = 3
End With
Range([B2], [B65536].End(xlUp)).Clear
[A1].Select
Application.ScreenUpdating = True
Exit Sub
e:
MsgBox "There are no duplicates.", 64, "Time for a beer !"
[A1].Select
End Sub
Thanks,
Noir
Sub DuplicatesCheck()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range([A2], [A65536].End(xlUp)).Offset(0, 1).Formula = "=IF(COUNTIF(Sheet2!RC[-1]:Sheet2!R[598]C[-1],RC[-1])<>0,""Yes"",""No"")"
Dim theCol As Range, cell As Range, RtoSel As Range
Dim LtoSel As String
Set theCol = Range(Range("B2"), Range("B65536").End(xlUp))
LtoSel = "Yes"
For Each cell In theCol
If Right(cell, 3) = LtoSel Then
If RtoSel Is Nothing Then
Set RtoSel = cell
Else
Set RtoSel = Application.Union(RtoSel, cell)
End If
End If
Next
On Error GoTo e
RtoSel.Offset(0, -1).Select
With Selection
.Font.FontStyle = "Bold"
.Interior.ColorIndex = 3
End With
Range([B2], [B65536].End(xlUp)).Clear
[A1].Select
Application.ScreenUpdating = True
Exit Sub
e:
MsgBox "There are no duplicates.", 64, "Time for a beer !"
[A1].Select
End Sub
Thanks,
Noir