Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Why doesnt this code work??

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Noir

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •