CODEWITHSUNDEEP
excelvba
Hidayat Ullah
Hidayat Ullah

Reputation: 157

How to compare two range values for equality?

Following is my code

Data = wb.Worksheets(1).Range("B3:E6").Value
targetValue = ActiveSheet.Range(targetcellL, targetcellR).Value

If Data = targetValue Then
     MsgBox "Match Found"

End If

The if condition give me error of "Runtime error 13 Type Mismatch" How can i compare two range value?

Upvotes: 0

Views: 428

Answers (2)

JohnyL
JohnyL

Reputation: 7132

Function ArraysEqual() As Boolean
    Dim cell As Range, rng1 As Range, rng2 As Range
    ArraysEqual = True
    For Each cell In rng1
        If WorksheetFunction.CountIf(rng2, cell.Value) = 0 Then
            ArraysEqual = False
            Exit Function
        End If
    Next
End Function

Upvotes: 0

DisplayName
DisplayName

Reputation: 13386

you have to check every item

as follows (you may want to add some check to ensure arrays sizes are the same):

Data = wb.Worksheets(1).Range("B3:E6").Value
targetValue = ActiveSheet.Range(targetcellL, targetcellR).Value

Dim i As Long, j As Long
Dim match As Boolean
match = True
For i = LBound(Data,1) to UBound(Data,1)
    For j = LBound(Data,2) to UBound(Data,2)    
        If Data(i, j) <> targetValue(i, j) Then
            match = False
            Exit For
        End If
    Next
    if Not match Then Exit For
Next
If match Then MsgBox "Match Found"

as for the "short way" you seem to be after, you may consider using a helper Function():

Data = wb.Worksheets(1).Range("B3:E6").Value
targetValue = ActiveSheet.Range(targetcellL, targetcellR).Value

If DoArraysMatch(Data, targetValue) Then MsgBox "Match Found"

and here's the heleper DoArraysMatch() function:

Function DoArraysMatch(arr1 As variant, arr2 As Variant) As Boolean
    Dim i As Long, j As Long
    Dim match As Boolean

    match = True
    For i = LBound(arr1,1) to UBound(arr1,1)
        For j = LBound(arr1,2) to UBound(arr1,2)    
            If arr1(i, j) <> arr2(i, j) Then
                match = False
                Exit For
            End If
        Next
        if Not match Then Exit For
    Next
    DoArraysMatch = match
End Function

Upvotes: 2

Related Questions