用括号表示法比较不同范围 [英] Compare different ranges with bracket notation

问题描述

我想将范围A2与N2，A3与N3，A4与N4进行比较，并希望将L2中的输出作为True/False作为 vs N2，并且A3与N3的比较结果必须在L3中.

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.

N2和N16之间的下一个比较，N3和N17之间的下一个比较....

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

推荐答案

使用方括号表示法时，例如[A1]您正在使用 方法:

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

使用方括号(例如，"[A1:C5]")与使用字符串参数调用Evaluate方法相同.

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

您可以通过在括号表示法指定的范围内设置Formula属性来使用此功能来完成所需的操作:

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

请注意使用:来获取Range-使用, s意味着您会这样做:

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

不如CompareCells1好.

您可以将范围分配给Variant，但不能像这样简单地比较两个数组-这将行不通:

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

您可以使用Transpose和Join方法

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

此处var1只是A2的值，而var2只是N2的值，即使您可以使用括号表示法设置范围L2:L10，也无法获得正确的比较结果您的要求.

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.

这篇关于用括号表示法比较不同范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！