vba - 如何比较 Excel-VBA 中两个单独的工作表列之间的字符串差异？

Question

Sheet1 Sheet2

上面我有两个图像链接，我从我的 excel 文档(Sheet1，Sheet 2)中捕获

这里基本上是一个简短的描述，我只想让我的宏比较两张表中的零件号(C 列)并找出差异。并且当在两个工作表之间检测到字符串差异时，它将突出显示 BOM 列表的两个工作表上的行，以向用户指示零件编号(C 列)中的差异。但这也是一个问题，如图所示，有些行带有“空格”，循环必须注意这些行，以防止比较空字符串从而给出错误的结果。

对不起，如果您不清楚，我的英语和解释能力很差。有人可以指导我吗？我对从哪里开始或如何开始相当漫无目的，而且我必须在一周内完成这项工作，而无需事先了解 excel-VBA 编程理解。

更新:

我已经更新了我的帖子，有人可以看一下，并就如何更改代码以突出显示 A 列到 P 列的整行而不是 C 列范围值差异仅给出您的意见？

Sub differences() 

    Dim ws1 As Worksheet, ws2 As Worksheet 
    Dim lastRow1 As Integer, lastrow2 As Integer 
    Dim rng1 As Range, rng2 As Range, temp As Range, found As Range 

    Application.ScreenUpdating = False 

    Set ws1 = ThisWorkbook.Sheets("Sheet1") 
    Set ws2 = ThisWorkbook.Sheets("Sheet2") 

    lastRow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row 
    lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row 

    Set rng1 = ws1.Range("C21:C" & lastRow1) 
    Set rng2 = ws2.Range("C21:C" & lastrow2) 

    For Each temp In rng1 
        Set found = Find_Range(temp.Value, rng2, , xlWhole) 
        If found Is Nothing Then 
            temp.Interior.ColorIndex = 3 
        End If 
    Next temp 

    For Each temp In rng2 
        Set found = Find_Range(temp.Value, rng1, , xlWhole) 
        If found Is Nothing Then 
            temp.Interior.ColorIndex = 3 
        End If 
    Next temp 


End Sub 

Function Find_Range(Find_Item As Variant, Search_Range As Range, Optional LookIn As Variant, Optional LookAt As Variant, Optional MatchCase As Boolean) As Range 

    Dim c As Range 
    Dim firstAddress As String 

    If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False 

    With Search_Range 
        Set c = .Find(What:=Find_Item, LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=MatchCase, SearchFormat:=False) 
        If Not c Is Nothing Then 
            Set Find_Range = c 
            firstAddress = c.Address 
            Do 
                Set Find_Range = Union(Find_Range, c) 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing And c.Address <> firstAddress 
        End If 
    End With 
End Function 

Answer 1

由于我看不到图像，我会假设您正在尝试做的是检查另一个列表中是否存在部件号，如果不存在则突出显示它。

在我的脑海中，这就是你基本上需要做的事情。

Public Sub Test()
    CompareRange Sheet1.Range("A2", "A8"), Sheet2.Range("A2", "A8")
End Sub


Public Sub CompareRange(range1 As Range, range2 As Range)
    Dim CompareCell As Range
    Dim CheckCell As Range
    Dim CellFound As Boolean
    For Each CompareCell In range1.Cells
        CellFound = False
        For Each CheckCell In range2.Cells

            If CheckCell.Text = CompareCell.Text Then
                CellFound = True
            End If
        Next CheckCell
        If Not CellFound Then
            CompareCell.Interior.Color = vbYellow
        End If
    Next CompareCell
End Sub

需要注意的一点是，此函数假定您有一个列范围。否则它将检查您范围内的所有单元格，并且可能会突出显示超出您预期的内容。

编辑
至于突出显示行
尝试将此添加到您的查找循环中

Dim CompareSheet as Worksheet 'Add at top of function

'Add to the For Each Loop
Set CompareSheet = temp.Worksheet
CompareSheet.Range("A" & temp.Row, "P" & temp.Row).Interior.ColorIndex = 3