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]

...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?

This one is easy, take out the quotes around "path".

[COLOR=#574123][I]Set wb1 = Workbooks.Open("path")[/I][/COLOR]

should be

[COLOR=#574123][I]Set wb1 = Workbooks.Open(path)[/I][/COLOR]

 

[kkmoslephour]

Oh yes thanks! that worked well! Do you know how to indicate a cell address, is there such thing as cell.address, also is there such usage of if..else statements in vba? here is what I am trying to do, but not sure if it makes sense or not. If the cell adress is table_start (which is the 'item' cell) address leave the background color of the cell the same else change it to blue.

If diffB Then
        DiffCount = DiffCount + 1
        If(cell.address = Table_Start)
            ws1.Cells(i, c).Interior.ColorIndex = 1
        else
            ws1.Cells(i, c).Interior.ColorIndex = 20
        
     End If
End If

 

[Cindy Ellis]

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]

Hi Cindy,

Hope you had a good weekend, one question, do you know why I get a type mismatch when I do the following:

If Table_Start Then ws1.Cells(i, c).Interior.ColorIndex = 1

Table_Start contains the address of the 'Item (' cell, am I not allowed to use it in the if statement?

 

[kkmoslephour]

Nevermind i figured it out it is supposed to be like this

If ws1.Cells(i, c) = Table_Start Then ws1.Cells(i, c).Interior.ColorIndex = 1

but this is still not changing my cell to black. I dont want my Item line to change colors if they see a difference, I want it to retain it black color. Does the below code make sense?

If diffB Then
        DiffCount = DiffCount + 1
        ws1.Cells(i, c).Interior.ColorIndex = 20
        'ws1.Cells(i, c).Font.Bold = True
     ElseIf ws1.Cells(i, c) = Table_Start Then ws1.Cells(i, c).Interior.ColorIndex = 1

 

[kkmoslephour]

Nevermind again, I got it. I had to check the cell.address to see if it matches the table_start address. Seems like I keep answering my own questions

 If diffB Then
        DiffCount = DiffCount + 1
        ws1.Cells(i, c).Interior.ColorIndex = 20
        'ws1.Cells(i, c).Font.Bold = True
    If ws1.Cells(i, c).Address = Table_Start Then ws1.Cells(i, c).Interior.ColorIndex = 1
    'MsgBox ws1.Cells(i, c).Address

 

[Cindy Ellis]

Nevermind again, I got it. I had to check the cell.address to see if it matches the table_start address. Seems like I keep answering my own questions

It seems like you're learning very quickly...you should be able to start answering other peoples' questions soon
Sorry it took me so long to get back to you; I've been travelling on business and my time hasn't quite been my own.

 

[kkmoslephour]

Thanks for all your help, this part of the code is complete, however I am going to utilize vba to make my life easier since it so convenient. I dont know if I should post a new thread or continue here, but for my next program instead of comparing two excel sheets I want to compare one excel sheet with one text file and parse the text file into on new excel sheet (each field on the text file is separated by a certain amount of spaces) and then highlight the differences. Do you think something like this would be possible?

 

[Cindy Ellis]

Definitely possible, but a new thread will be best. It will get more attention, and will be easier to correlate the title to the topic.

 

[kkmoslephour]

Okay, I will post a new thread, one more question regarding this one if you don't mind I am trying to implement an errorHandler for this program just in case the user selects cancel when choosing the excel file. However I keep getting the error handler to pop up even when there is no error occurring (error handler pops up before I go into selecting workbooks)... Do you happen to know why this is happening?

  'Opening first workbook...
  'Must put exact workbook name and location.
  'Place comparison workbook here..
 On Error GoTo ErrorHandler
  MsgBox "Please select comparison workbook"
  With Application.FileDialog(msoFileDialogOpen)
    .Show
    If .SelectedItems.Count = 1 Then
        path1 = .SelectedItems(1)
    End If
  End With
  
ErrorHandler:
  Msg = "No folder selected, you must select a folder for program to run"
  Style = vbError
  Title = "Need to Select Folder"
  Response = MsgBox(Msg, Style, Title)
  MsgBox path1