CODEWITHSUNDEEP
excelvba
Manu
Manu

Reputation: 13

Compare different ranges with bracket notation

I want to compare range A2 with N2, A3 with N3 , A4 with N4 and want the output in L2 as True/False for the compared result of A2 vs N2 and the comparison result of A3 with N3 has to be in L3 and like that.

Also the next comparison between N2 with N16 , N3 with N17....

Please provide your feedback so that I can proceed further.

I have written the below code but selecting the range is not working for me:

Sub CompareCells()
    If [A2,A3] = [N2,N3] Then
        [L2,L3] = "yes"
    Else
        [L2,L3] = "no"
    End If
End Sub

Upvotes: 2

Views: 732

Answers (2)

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

When you use bracket notation e.g. [A1] you are using the EVALUATE method:

Using square brackets (for example, "[A1:C5]") is identical to calling the Evaluate method with a string argument.

You can use this to do what you want by setting the Formula property on a Range specified by bracket notation e.g.:

Option Explicit

Sub CompareCells1()

    [L2:L10].Formula = "=IF(A2=N2,""yes"",""no"")"

End Sub

Note use of : to get a Range - using ,s means you would do:

Option Explicit

Sub CompareCells2()

    ' you need to type each cell reference upto L10....
    [L2, L3, L4, L5].Formula = "=IF(A2=N2,""yes"",""no"")"

End Sub

Which isn't as good as CompareCells1.

You can assign the range to a Variant but you can't simply compare two arrays like that - this won't work:

Option Explicit

Sub CompareCells3()

    Dim var1, var2

    var1 = [A2:A10]
    var2 = [N2:N10]

    ' throws a Type Mismatch error
    If var1 = var2 Then
        ' this will never happen
    End If

End Sub

You can compare var1 and var2 per the faulty example above by using the Transpose and Join method suggested by Siddarth in his comment, per Tim Williams post but I think CompareCells1 method is the easiest for you if you need, or want, to use bracket notation.

Using ,s to do comparison will result in a false positive. For example:

Option Explicit

Sub CompareCells4()

    Dim var1, var2

    var1 = [A2,A10]
    var2 = [N2,N10]

    ' creates a string from the range values
    If var1 = var2 Then
        ' this is a false positive - check the value of var1 and var2
        [L2:L10] = "False positive"
    End If

End Sub

Here var1 is just the value of A2 and var2 is just the value of N2 meaning even though you can set the range L2:L10 with bracket notation doesn't get you the correct comparison per your requirement.

Upvotes: 1

YowE3K
YowE3K

Reputation: 23984

As stated in comments, VBA can't handle array manipulations such as [A1,A2] = [N2,N3] or [L2,L3] = "yes", so you will either need to do each test individually, such as:

Sub CompareCells()
    If [A2] = [N2] Then [L2] = "yes" Else [L2] = "No"
    If [A3] = [N3] Then [L3] = "yes" Else [L3] = "No"
    If [A4] = [N4] Then [L4] = "yes" Else [L4] = "No"
End Sub

or you will need to have a loop, such as:

Sub CompareCells()
    Dim r As Long
    For r = 2 To 4
        If Cells(r, "A").Value = Cells(r, "N").Value Then
            Cells(r, "L").Value = "yes"
        Else
            Cells(r, "L").Value = "no"
        End If
    Next
End Sub

There isn't sufficient information in your question to know how to extend this code to compare N2 with N16, etc, but it should be easy enough for you to continue the above methods to handle it.

Upvotes: 1

Related Questions