2

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
Community
  • 1
  • 1
Manu
  • 13
  • 3
  • Yes, that sounds fairly simple. Is there anything you need assistance with? – YowE3K Jun 17 '17 at 06:23
  • Yes I want the vba code to execute the above.. – Manu Jun 17 '17 at 06:25
  • compare? What? =, >, <, >=, <=, <> ? – Gowtham Shiva Jun 17 '17 at 06:27
  • (a) There isn't enough info in the question to write VBA code for you (b) SO isn't a "code for me" site anyway. We are willing to help you fix problems you have with **your** code, but you need to tell us what problems you are having. – YowE3K Jun 17 '17 at 06:28
  • I want to compare cell A2 with N2 , A3 with N3 , A4 with N4 like that upto row 30. – Manu Jun 17 '17 at 06:30
  • I am trying to write this code but this is not working.Sub CompareCells() If [A2,A3] = [N2,N3] Then [L2,L3] = "yes" Else [L2,L3] = "no" End If End Sub here I am not able to set the range for this array – Manu Jun 17 '17 at 06:31
  • (a) If that is your code, you should add it to the question. (b) VBA doesn't handle array manipulations - use a loop - and post your new code if you have a problem. – YowE3K Jun 17 '17 at 06:33
  • can it be possible in this code to select the range of cells.? – Manu Jun 17 '17 at 06:34
  • Yes, VBA code can select the range of cells if that is what you need to do. Update your question with your current code, and say what isn't working, and then we can assist you. – YowE3K Jun 17 '17 at 06:35
  • okok I will try with the Loop and come up if still faces any issue. – Manu Jun 17 '17 at 06:36
  • yes done the changes in the questions – Manu Jun 17 '17 at 06:39
  • You can compare ranges like `[A2:A30]` with `[N2:N30]`. If that is what you are after then try this `MsgBox Join(Application.Transpose(ActiveSheet.Range("A2:A30").Value), Chr(0)) = Join(Application.Transpose(ActiveSheet.Range("N2:N30").Value), Chr(0))` Credit where due... This beautiful piece of code was suggested by @TimWilliams [HERE](https://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet/19396257#19396257) – Siddharth Rout Jun 17 '17 at 09:05

2 Answers2

1

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.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • above query worked for me..:) 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 – Manu Jun 17 '17 at 18:41
  • Thanks a lot guys...since t was weekend but still i got a wonderful response from you all..thanks a lot... – Manu Jun 17 '17 at 18:43
1

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.

Robin Mackenzie
  • 18,801
  • 7
  • 38
  • 56