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

上面我有两个图像链接，我从我的Excel文档中捕获（Sheet1，Sheet 2）

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

对不起我对英语的不良掌握和解释，如果你不清楚的话 . 有人可以指导我这个我相当漫无目的地在哪里或如何开始，我必须在一周内完成这个，而无需事先了解excel-VBA编程理解 .

Updated:

我已经更新了我的帖子，有人可以看看，并告诉我你如何更改代码以突出显示列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

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

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

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

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

回复于 2024-04-26T22:28:06+08:00

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

1 回答

相关问题