比较多列 VBA EXCEL（嵌套循环）

debugcn 发表于 Dev

杰克

我有以下代码可以同时比较两个不同的工作表。

Dim compareRange As Range
Dim toCompare As Range
Dim rFound As Range
Dim cel As Range

Set compareRange = Worksheets("sheet2").Range("A1:A" & Lastrow3)
Set toCompare = Worksheets("sheet3").Range("A1:A" & Lastrow4)
Set rFound = Nothing

For Each cel In toCompare
    Set rFound = compareRange.Find(cel)
    Z = compareRange.Find(cel).Row
    If Not rFound Is Nothing Then
        cel.EntireRow.Interior.Color = vbGreen
        Set rFound = Nothing
    End If
Next cel

但是，这仅在我想同时比较 A 列的 C'd 和 D 列时才比较 A 列，并且仅在所有三个匹配时才通过。工作表具有重复值，这就是为什么我需要一次比较 3 个项目但有些列是相同的。我必须使用嵌套循环。知道从哪里开始吗？

我以为我可以做类似的事情

Set compareRange = Worksheets("sheet2").Range("A1:A, C1:C, D1:D" & Lastrow3)

但显然我不能

米格

实际上，您不是在比较代码中的其他两列。试试下面的代码。

Sub Demo()
    Dim compareRange As Range, toCompare As Range
    Dim lastRow1 As Long, lastRow2 As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Long, j As Long

    Set ws1 = ThisWorkbook.Worksheets("Sheet2")
    Set ws2 = ThisWorkbook.Worksheets("Sheet3")
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

    Set compareRange = ws1.Range("A1:A" & lastRow1)
    Set toCompare = ws2.Range("A1:A" & lastRow2)

    For i = 1 To lastRow2
        For j = 1 To lastRow1
            If ws2.Cells(i, 1) = ws1.Cells(j, 1) And ws2.Cells(i, 3) = ws1.Cells(j, 3) And ws2.Cells(i, 4) = ws1.Cells(j, 4) Then
                ws2.Cells(i, 1).Interior.Color = vbGreen
                Exit For
            End If
        Next j
    Next i
End Sub

如果有什么不清楚的，请告诉我。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。