Check with VBA if each value of two different ranges are a match or not

[Jirka79]

Hi all,

I'm just trying to find a simple code that pops up a msg box saying if all the values of one range are matching all the values of another range or not.

I know I can do that with a formula, but I need to do it with vba because I might use it in another applications.

Sheets("Sheet1").Range("A1:A8")

Sheets("Sheet1").Range("B1:B8")

So, if all the different values that might contain each cell of the range from A1 to A8 matches with the same values of each cell of the range B1:B8, then I want to pop up a message saying "All values matched" and if just one or more values are not maching then I need the message "One or more values are different"

This means that a range match is fulfilled when A1=B1, A2=B2, A3=B3... up to A8=B8.

I have a code, but is too messy to share it here with you... and I'm struggling with the code For / For each / Next commands.... because I don't know how to use them properly.

Can you please help me?

Thank you in advance!

 

[Jirka79]

OK, I found myself the solution :D

Sub RowCompare()

    Dim i As Long
    Dim ComparisionResult As Boolean

    For i = 1 To 8
        If IIf(Cells(i, 1).Value = "", "", Cells(i, 1).Value) = IIf(Cells(i, 2).Value = "", "", Cells(i, 2).Value) Then
            ComparisionResult = True
        Else
            ComparisionResult = False
            Exit For
        End If
    Next i

   If ComparisionResult Then MsgBox "Both columns match!", vbInformation, "Match!" Else _
        MsgBox "different", vbInformation, "Match!"

End Sub

Original solution from another website here.