比较两个不同工作簿中的字符串

Question

我必须使用VBA比较Excel中两个工作簿的用户名。如何做到这一点？

例如：

• 在工作簿1中：A列包含10个名称
• 在工作簿2中：A列包含10个名称

我需要根据匹配情况将每个工作簿中B列的单元格涂成绿色或红色。

Answer 1

我"认为"你的意思是这样的，但我知道我总是需要谨慎。下面的代码应该可以工作，但您可能希望更动态地设置范围。

Option Explicit
Sub Compare_Names()
Dim oBook_1             As Excel.Workbook
Dim oBook_2             As Excel.Workbook
Dim oRange_1            As Range
Dim iRange_1_Rows       As Integer
Dim oRange_2            As Range
Dim iRange_2_Rows       As Integer
Dim vArray              As Variant
Dim vArray_Found        As Variant
Dim iCnt                As Integer
Dim iCnt_B              As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set oBook_1 = Workbooks.Open("U:/Names_1.xls")
    Set oRange_1 = oBook_1.Sheets(1).Range("A1:A5") 'Can be dynamically set when needed
    iRange_1_Rows = oRange_1.Rows.Count
    ReDim vArray(1 To iRange_1_Rows, 1 To 1)
    vArray = oRange_1
    Set oRange_1 = Nothing
oBook_1.Close
Set oBook_1 = Nothing
Set oBook_2 = Workbooks.Open("U:/Names_2.xls")
    Set oRange_2 = oBook_2.Sheets(1).Range("A1:A5")
    iRange_2_Rows = oRange_2.Rows.Count
    For iCnt = 1 To iRange_1_Rows
        For iCnt_B = 1 To iRange_2_Rows
            ReDim vArray_Found(1 To iRange_2_Rows, 1 To 1)
            If Trim(vArray(iCnt, 1)) = Trim(oRange_2(iCnt_B)) Then
                oRange_2(iCnt_B).Interior.Color = vbGreen
                vArray(iCnt_B, 1) = True
            End If
        Next iCnt_B
    Next iCnt
    For iCnt = 1 To iRange_2_Rows
        If vArray(iCnt, 1) <> True Then
            oRange_2(iCnt).Interior.Color = vbRed
        End If
    Next iCnt
    Set oRange_2 = Nothing
oBook_2.Save
oBook_2.Close
Set oBook_2 = Nothing
End Sub

如果你想要一个不区分大小写的比较，你可以使用：

if UCase(Trim(vArray(iCnt, 1))) = UCase(Trim(oRange_2(iCnt_B))) Then