Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Comparing two excel sheets, run time error '1004'

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Comparing two excel sheets, run time error '1004'

    Hi guys this is my first post in this forum and I am new to macros, so please any feed back would help a lot

    I have a couple questions trying to program this, but let me ask them one at a time now. First, I am trying to do a file comparison macros on excel and highlight differences, however ran into a little error while doing this. In the code i have stated which line it is having the problem, I have read online articles and many people say that it is not good practice to use the '.select' so i am suspecting if that is why my error is occurring?

    Code:
    Option Explicit
    
    
    Sub Compare()
    
    
      Dim wb1 As Workbook, wb2 As Workbook
      Dim ws1 As Worksheet, ws2 As Worksheet
      Dim diffB As Boolean
      Dim r As Long, c As Integer, m As Integer, i As Integer
      Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
      Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
      Dim rptWB As Workbook, DiffCount As Long
      Application.ScreenUpdating = False
      Application.StatusBar = "Creating the report..."
      Application.DisplayAlerts = True
      
      Set wb1 = Workbooks.Open("C:\A319")
      Set ws1 = wb1.Worksheets("BuildSheet")
      With ws1.UsedRange
        lr1 = .Rows.Count
        lc1 = .Columns.Count
      End With
      
      Set wb2 = Workbooks.Open("C:\A320")
      Set ws2 = wb2.Worksheets("BuildSheet")
      With ws2.UsedRange
        lr2 = .Rows.Count
        lc2 = .Columns.Count
      End With
      maxR = lr1
      maxC = lc1
      If maxR < lr2 Then maxR = lr2
      If maxC < lc2 Then maxC = lc2
      DiffCount = 0
      For c = 1 To maxC
        For i = 2 To lr1
          diffB = True
          Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..."
            For r = 2 To lr2
              cf1 = ""
              cf2 = ""
              On Error Resume Next
              cf1 = ws1.Cells(i, c).FormulaLocal
              cf2 = ws2.Cells(r, c).FormulaLocal
              On Error GoTo 0
              If cf1 = cf2 Then
                diffB = False
                ws1.Cells(i, c).Interior.ColorIndex = 0
                ws1.Cells(i, c).Select
                Selection.Font.Bold = False
                Exit For
              End If
            Next r
     
         If diffB Then
            DiffCount = DiffCount + 1
            ws1.Cells(i, c).Interior.ColorIndex = 19
            ws1.Cells(i, c).Select  ----------------------------------------> error occurred here 
            Selection.Font.Bold = True
         End If
        Next i
      Next c
    Application.StatusBar = "Formatting the report..."
    'Columns("A:IV").ColumnWidth = 10
    m = DiffCount - 1
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox m & " cells contain different values!", vbInformation, _
    "Compare " & ws1.Name & " with " & ws2.Name
    End Sub

  2. #2
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    Hello, and Welcome to Mr. Excel!
    I love questions where the question is already answered . Yes, the problem is with the Select statements, because ws2 is the active workbook (it got opened last, so it's on top).
    Just combine the select (both places it happens), with the next line, changing to
    Code:
    ws1.Cells(i,c).Font.Bold = True
    There is another bit that looks like a logic error...you are only incrementing Diffcount after an error is found, but when you report it out, you're subtracting one. That may be because there will be a known difference between the files that you want to ignore, but you may want to add a comment to the code so that someone else maintaining it at some point in the future will be aware of the reason behind it.
    Hope that helps,
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    May 2014
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    Hi Cindy, thanks for the fast reply, my code works now with no errors now However here is my second question, I would like to do more customization with my code to make it more convenient for others to use. Let's say I have two excel files to compare, and both files have similar format, however I am only interested in comparing a table from both workbooks (so I dont have to cut and paste the table into a new workbooks and do the compare, which is what i am doing now), both tables start with the word 'item' and I am not interested in anything above or after the the table. My question is, is there anyway I can write a macros to scan through both documents and only start comparing after the word 'item'? I've tried searching online some people said I could use the find command, and I am thinking using IF statement with the find command ? but i am not entirely sure how that would work. Can you give me some insight please , thanks!
    Last edited by kkmoslephour; May 8th, 2014 at 05:48 PM.

  4. #4
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    A few questions:
    Is ITEM the only word in the cell where the table starts?
    Is the structure of the table consistent (the same number of rows & columns)?
    You can use Cells.Find in several ways. The first one below assigns returns a cell address, and blindly finds the first cell address where ITEM is the only text in the cell:
    Code:
    Table_Start = Cells.Find(What:="ITEM", After:=Range("A1"), LookAt:=xlWhole).Address
    If there's a chance that it won't find it, you would trigger an error, and dump the user into the VBA editor (not good). To prevent that from happening, you can look for it first with "worksheetfunction.countif" :
    Code:
      
    If WorksheetFunction.CountIf(Cells, "ITEM") <> 0 then
        Table_Start = Cells.Find(What:="ITEM", After:=Range("A1"), LookAt:=xlWhole).Address
    Else
        'handle error here
    End if
    Hope that helps,
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    May 2014
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    No Item would not be the only word in the cell, here is how the cell would look like
    Item (57 items)

    Some workbooks might have more items than others but the table will always start with Item (xx items) format. Also the tables do not always have have the same rows and columns some might have more than the other. So once I find the address I can start my comparison with the address i get from the output?

  6. #6
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    Yes, you should be able to start your comparison from that address. Is everything below and to the right of the the "Item (xx items)" identifier?
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    May 2014
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    Yes everything is below and the the right of the 'Item" identifier.

  8. #8
    New Member
    Join Date
    May 2014
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    Also since my "Item" string might have different items counts inside how would be the best way to call identify it? since I wouldnt be able to just the word "Item" to search for. And another question is I would have to get the cell address for both workbooks correct and start my comparison from there on correct?

  9. #9
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    You can search for XLPart instead of XLWhole, if there aren't other cells that contain the word "Item (". If there are other cells (especially above or to the left) that have that string (Item, then a space, then a left parenthesis), but aren't relevant, we can use wildcards in the search, but I need to test out that approach before going that route.
    And yes, you will need to separately identify the 2 starting points. It adds complexity at the coding stage, but if this will be executed repeatedly, it can be worth it to simplify the execution. I can't work on it in detail for a few hours (at work right now), but I should be able to get back to you by this evening at the latest (it's 9:30 AM right now where I am)
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  10. #10
    New Member
    Join Date
    May 2014
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel sheets, run time error '1004'

    One more question is that I'm trying to have user select the path of the excel file, in the message box it shows the correct path, however when I put path variable into the open file it thinks my file name is path instead of the location of the path why is that?

    Code:
    With Application.FileDialog(msoFileDialogOpen)
        .Show
        If .SelectedItems.Count = 1 Then
            path = .SelectedItems(1)
        End If
      End With
      MsgBox path
      
      
      'Set wb1 = Workbooks.Open("C:\Build_71706a_TACA_A319-100")
      Set wb1 = Workbooks.Open("path")

Some videos you may like

User Tag List

Tags for this Thread

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
  •