Comparing two excel sheets, run time error '1004'

[kkmoslephour]

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?

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

 

[Cindy Ellis]

The first way to test it is to comment out the On Error line, and see what error is triggered. If there's no error, and it makes it to the filepicker, then I really don't know, but try moving the On Error line below the msgbox. Also, make sure you have "On Error Goto 0" after your "End With" so that error handling is restored to normal.
I put the code above into a sub by itself, and it ran just fine.

 

[kkmoslephour]

Hi Cindy,

Hows everything? I have just recently came back to this code and was wondering how to use two table_starts to start comparing both tables, right now I only have one, but I don't know how to indicate the other table.
Where in the code could I specify the second worksheet table?

Table_Start = Cells.Find(What:="Item (", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False).Address

Here's one way to find the address of the first cell of the table:

table_start = Cells.Find(What:="Item (", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Address
MsgBox table_start   'this is just here so you can see what the return value is...delete or comment out later

Of course, you would have 2 of these, maybe Table1_Start for the first table and Table2_Start for the other one.

For your purposes, I think it might be useful to convert it to a row number and column number, so that you can more easily identify the cells to have the background color changed. If you want to do that, the syntax is:

table_start_row = Cells.Find(What:="Item (", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row
table_start_column = Cells.Find(What:="Item (", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Column

You can then use these as arguments to various functions (the Cells function in particular) to cycle through the range of cells.

 

[kkmoslephour]

Actually nvm I figured it out again! :D

 

[Cindy Ellis]

Thanks for letting me know. Still St work, but planning to work on it tonight.