CODEWITHSUNDEEP
excelvba
Devilhunter
Devilhunter

Reputation: 39

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

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

Upvotes: 2

Views: 1516

Answers (1)

Odatas
Odatas

Reputation: 178

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

Upvotes: 0

Related Questions