Why doesnt this code work??

[Noir]

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

[Tom Urtis]

Noir,

I recognize your macro as 2 separate pieces of code I have posted on this board recently, each for two different questions that were asked. Each portion of code works based on the question that was asked for which that code was intended. It is hard (at least for me) to say why you are experiencing a problem with the two combined, so please email me your spreadsheet and I'll be happy to look at it and see exactly what you are trying to do.

My email is TomUrtis@attbi.com.

[Noir]

Thanks Tom, I will e-mail my spreadsheets to you.

Noir

[Noir]

Tom,
I received a Delivery Failure message when i tried to send you an e-mail.

The problem is pretty simple though. The code will not recognize all of the serial numbers within both sheets. It only recognizes a few. Maybe i did not copy your code to the right place. Does it go in "This Workbook" or in a Module?

Noir

[Tom Urtis]

Noir,

I received your email; what you call a "spreadsheet" is commonly called a "workbook". You sent me two workbooks, and now I see that you were trying to compare two lists of serial numbers, each located in separate workbooks. The macro code assumed the two different lists were on separate spreadsheets in the same workbook.

For this sort of comparison work you'd be better off housing (at least temporarily) all information in the same workbook, and then the macro will work.

To do that, try prefacing the code you now have with a few lines to import the serial numbers from one file to another. Importing is better than paste links in my opinion; all those formulas slow down the destination file, and annoy other users with that "Update links?" prompt (which can be bypassed but that's another story).

Anyway, insert this code at the beginning of your macro, assuming the destination file is the one named “3rd_Floor.xls”, where the macro is located:

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Workbooks.Open Filename:="C:YourFilePath5th_Floor.xls"
ThisWorkbook.Worksheets("Sheet2").Range("A1:A1000").Value = _
Workbooks("5th_Floor.xls").Worksheets("Sheet1").Range("A1:A1000").Value
Workbooks("5th_Floor.xls").Close


‘Sidebar, your existing macro code goes here


‘Place this at the end of the macro:

‘Optional, just to clear A1:A1000 of text data in Sheet2, although it’s too small to slow down your file:
Worksheets("Sheet2").Range("A1:A1000").Clear

‘Not optional; insert this at the very end:
ThisWorkbook.Save
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True


Modify “YourFilePath” with the file path where 5th_Floor.xls is located.

Finally, be sure to not change the file path, workbook name, or “Sheet1” sheet name of the 5th_Floor.xls file. There is alternate code to not worry about the sheet name tab changing but this code here will be easier for you and other users to track. If the aforementioned does get changed, you will receive an out of range run time error, reminding you that you must have the aforementioned reflect what the macro code is instructed to look for. Down the road we can put in an error trap to make the run time error less ugly, but that’s probably superfluous for now.

Hope this helps; let us know how it turns out.