Why doesnt this code work??

Noir

Active Member
Joined
Mar 24, 2002
Messages
362
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top