Compare multiple worksheets from different excel files-CodePudding

I need one more step to complete this task in vba .. So, guys I need some assistance here , any help will be welcomed.

I need to compare multiple worksheets between two excel files. First [for cycles ] is to check if that sheet name is in both excel files and if so then compared/ find differences in the cell , else if that sheet is not exist in both excel files then color the sheet with yellow colour.

But I'm stuck here.. I'm constantly getting an error: 'Invalid Next control variable reference'. Maybe my method here to compare sheet name is not probably good defined. Here is my code , I hope this makes sense..

Option Explicit
Sub Compare_Two_Excel_Sheets()
    'Define Fields
    Dim Flag As Double
  
    Dim iR As Double, iC As Double, oRw As Double, fone As Double, ftwo As Double
    Dim iRow_M As Double, iCol_M As Double, ifiles_one As Double, ifiles_two As Double
    
    Dim s1 As Workbook, s2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim s3 As Workbook
    Flag = 0
    
    'Path
    Set s1 = Workbooks.Open(Filename:="File1_Path.xlsx")
    Set s2 = Workbooks.Open(Filename:="File2_Path.xlsx")
   
   'Sheet1_compare_sheetName
   
    Set ws1 = s1.Sheet(1)
    Set ws2 = s2.Sheet(1)
    
    ifiles_one = ws1.Name
    ifiles_two = ws2.Name
    
    For fone = 1 To ifiles_one
    For ftwo = 1 To ifiles_two
    
    ws1.Names(fone, ftwo).Interior.Color = xlNone
    ws2.Names(fone, ftwo).Interior.Color = xlNone
    
 If ws1.Names(fone, ftwo) = ws2.Names(fone, ftwo) Then
          
    
    'Sheet1
    'count columns and rows
    iRow_M = ws1.UsedRange.Rows.Count
    iCol_M = ws1.UsedRange.Columns.Count
    
    For iR = 1 To iRow_M
    For iC = 1 To iCol_M
        ws1.Cells(iR, iC).Interior.Color = xlNone
        ws2.Cells(iR, iC).Interior.Color = xlNone
        
        If ws1.Cells(iR, iC) <> ws2.Cells(iR, iC) Then
           ws1.Cells(iR, iC).Interior.Color = vbYellow
           ws2.Cells(iR, iC).Interior.Color = vbYellow
           
           oRw = oRw   1
         
          Flag = Flag   1
         
        End If
        
    Next iC
    Next iR
    
    If Flag > 0 Then
        VBA.Interaction.MsgBox "Differences exist, please check the yellow fields in Sheet1!"
        Else: VBA.Interaction.MsgBox "No differences found!"
    
  End If

End If
Next fone
Next ftwo

Else
ws1.Names(fone, ftwo).Interior.Color = vbYellow
           ws2.Names(fone, ftwo).Interior.Color = vbYellow
 


End Sub

CodePudding user response：

Option Explicit
Sub Compare_Two_Excel_Sheets()
    'Define Fields
    Dim Flag As Long, t0 As Single
    Dim iR As Long, iC As Long, oRw As Long
    Dim iRow_M As Long, iCol_M As Long
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
   
    t0 = Timer 'start time
    Flag = 0
    
    'Path
    Set wb1 = Workbooks.Open(Filename:="File1_Path.xlsx")
    Set wb2 = Workbooks.Open(Filename:="File2_Path.xlsx")
    
    ' compile list of wb2 sheet names
    Dim dict As Object, sName As String
    Set dict = CreateObject("Scripting.Dictionary")
    For Each ws2 In wb2.Sheets
        dict.Add ws2.Name, 0
        ws2.Cells.Interior.Color = xlNone
    Next
   
    ' compare with wb1 sheet names
    Application.ScreenUpdating = False
    For Each ws1 In wb1.Sheets

        sName = ws1.Name
        iRow_M = ws1.UsedRange.Rows.Count
        iCol_M = ws1.UsedRange.Columns.Count
        ws1.Cells.Interior.Color = xlNone
        
        If dict.exists(sName) Then
            dict(sName) = 1 ' mark as match
            Set ws2 = wb2.Sheets(sName)
            
            If ws2.UsedRange.Rows.Count > iRow_M Then
                iRow_M = ws2.UsedRange.Rows.Count
            End If
            
            If ws2.UsedRange.Columns.Count > iCol_M Then
                iCol_M = ws2.UsedRange.Columns.Count
            End If
            
           'compare sheets ws1 ws2
            For iR = 1 To iRow_M
                For iC = 1 To iCol_M
           
                    If ws1.Cells(iR, iC) <> ws2.Cells(iR, iC) Then
                       ws1.Cells(iR, iC).Interior.Color = vbYellow
                       ws2.Cells(iR, iC).Interior.Color = vbYellow
                       
                       oRw = oRw   1
                       Flag = Flag   1
                       
                    End If
                Next iC
            Next
        Else
            ws1.Cells.Interior.Color = vbYellow
            Flag = Flag   1
        End If
    Next
    
    ' sheets in wb2 not matched
    For Each ws2 In wb2.Sheets
        sName = ws2.Name
        If dict(sName) = 0 Then
            ws2.Cells.Interior.Color = vbYellow
            Flag = Flag   1
        End If
    Next
    Application.ScreenUpdating = False

    Dim msg As String
    If Flag > 0 Then
        msg = "Differences exist, please check the yellow fields in Sheet1!"
    Else:
        msg = "No differences found!"
    End If
    MsgBox msg, vbInformation, Format(Timer - t0, "0.0") & " secs"
    
End Sub

CodePudding user response：

You say: I'm constantly getting an error: 'Invalid Next control variable reference'.

Your For ... Next loops fone and ftwo are incorrectly written as

    For fone = 1 To ifiles_one
    For ftwo = 1 To ifiles_two

    Next fone
    Next ftwo

The Next of the inner loop (ftwo) must be before the Nextof the outer loop (fone). Properly indented it is easy to get it right:

    For fone = 1 To ifiles_one
        For ftwo = 1 To ifiles_two
            ...
        Next ftwo
    Next fone