How to compare two range of cells in two different excel Worksheets?

Question

I've put my vba code here to show you my progress so far on the subject. This is intended to be a macro which activates on a commandbutton click.

Thank you for your suggestions, unfortunately I don't see if that flagged duplicate would solve my problem. I will try to clarify it the best as i can. In each Worksheets, in the same range there are two identical 9*9 width and height block of cells, and each cell is filled with a single number from 1 to 9 value. The values of the cells are identical too in both worksheets. What I would expect from my code to do is to compare the two ranges's each value in each cell, and if all of them are a match, write in msgbox success, else fail. I tried writing .Value after defining each range, but that doesn't work either.

I always get a runtime error 13: type mismatch. I was guessing this type of problem is related to that my values are not the same type, but I only have numbers in each declared range. I've googled the problem, but since i can't seem to figure out the root of this problem, I'm hoping You can be an answer to my dilemma.

Sub Check()    
    If Sheets("Sudoku").Range("B2:J10") = Sheets("Solution").Range("B2:J10") Then    
        MsgBox "Success!"
    Else    
        MsgBox "Fail!"    
    End If    
End Sub

Any kind of answer would be greatly appreciated! Thank you all for the answers, We finally came up with a solution! I'll put it below if anyone is interested in it. It is a slightly modified version of Odatas's version.

Sub Check()
Dim i As Integer
Dim j As Integer

For i = 2 To Sheets("Sudoku").Cells(Sheets("Sudoku").Rows.Count, "B").End(xlUp).Row
    For j = 2 To Sheets("Sudoku").Cells(2, Sheets("Sudoku").Columns.Count).End(xlToLeft).Column
        If Sheets("Sudoku").Cells(j, i).Value = Sheets("Megoldás").Cells(j, i).Value Then
        Else
            MsgBox ("Fail!")
            Exit Sub
        End If
    Next
Next
MsgBox ("Success!")

End Sub

What property of the range are you trying to compare? And what problem arises with your code? If it is the `.Value` property, you need to check each cell individually. If it is something else, then clarify your question. — Ron Rosenfeld, Nov 26 '18 at 11:07
Possible duplicate of [Fastest way to check if two ranges are equal in excel vba](https://stackoverflow.com/questions/22270693/fastest-way-to-check-if-two-ranges-are-equal-in-excel-vba) — Pragmateek, Nov 26 '18 at 11:09

score 0 · Accepted Answer · answered Nov 26 '18 at 14:12

You need to iterate through all values.

    Sub Check()
    Dim i As Integer
    Dim j As Integer


    For i = 1 To Sheets("Sudoku").Cells(.Rows.Count, "A").End(xlUp).Row
        for j= 1 to Sheets("Sudoku")..Cells(1, .Columns.Count).End(xlToLeft).Column
            If Sheets("Sudoku").Cells(j, i).value = Sheets("Solution").Cells(j, i).value Then

            Else If
                MsgBox("Fail")
                Exit Sub
            End If

        Next
    Next

    MsgBox("Success")
  End Sub

Thank you! This solution with slight modifications worked! I'll update the thread according to this, and put the working code in if anyone else wonders how to do it. — Devilhunter, Nov 26 '18 at 15:38

How to compare two range of cells in two different excel Worksheets?

1 Answers1