vba - 如何使用 VBA EXCEL 比较两列

Question

我需要一个宏VBA，它将在“B”列中获取一个代码，并在“E”列中逐行查找它，直到最后，如果它存在=>在“F”列中确定，否则为“FALSE”
B 列和 E 列中有很多代码。

我试过这段代码，但它不起作用

Sub ComparerCP()
Dim ws1 As Worksheet: Set ws1 = Worksheets("Feuil1")
Dim LastRow As Long
LastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
    If ws1.Cells(i, 2) = ws1.Cells(i, 5) Then 
        ws1.Cells(i, 6) = "OK"
        Else: ws1.Cells(i, 6) = "FALSE"
      End If
Next i
End Sub

Answer 1

你可以试试这两种方法。第一个VBA:

Public Sub ComparerCP()
    Dim rng As Range
    Dim arr As Variant
    Dim c

    ' Update to your sheet name
    With ActiveSheet
        ' Set the range that we want to check
        Set rng = .Range(.Cells(1, 2), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, 2))
        ' This array contains the range to check against
        ' We transpose the array to get a 1D array from the range
        arr = Application.Transpose(.Range(.Cells(1, 5), .Cells(.Cells(.Rows.Count, 5).End(xlUp).Row, 5)))

        ' Loop through check range and test if each value is in the range to check against
        For Each c In rng
            If IsInArray(c.Value2, arr) Then
                c.Offset(0, 1).Value2 = "Ok"
            End If
        Next c
    End With
End Sub


Public Function IsInArray(v As Variant, FilterArray As Variant) As Boolean
    IsInArray = (UBound(Filter(FilterArray, v)) > -1)
End Function

其次，您可以只使用 Excel 公式。在专栏 C我已经输入:

=IF(COUNT(MATCH(B1,$E:$E,0)),"Ok","")

输出相同的东西