excel vba根据唯一值比较两张纸

我有2张名为橙皮书数据警报和总橙皮书数据警报,两张表都有从A到M的数据,唯一值在两张表中的A列 . 在这里,我将橙皮书数据警报与总橙皮书数据警报进行比较 . 当匹配时,比较每个列的行单元格值,如果存在差异,则应在表橙色书籍数据警报列N中将代码写为更新,如果唯一值不可用,则应写入新添加列N ..下面是我试过的代码 . 它正在工作,如果一列的数据有任何更新,我需要帮助,以便我可以比较所有列,如果不存在唯一值,则写为新添加 .

Sub compare10() 
Dim w1 As Worksheet, w2 As Worksheet 
Dim c As Range, a As Range 


Set w1 = Sheets("total orange book data") 
Set w2 = Sheets("orange book data alerts") 
With w1 
    For Each c In .Range("a2", .Range("a" & Rows.Count).End(xlUp)) 
        Set a = w2.Columns(1).Find(c.Value, lookat:=xlWhole) 
        If Not a is nothing then
            if.cells(c.row,10).value<>w2.cells(a.row(a.row,10) then

            w2.Cells(a.Row, 14).Value = "update" 
        End If 
    Next c 
End With


End Sub

回答(1)

2 years ago

很抱歉为您推荐一个全新的代码,但下面的代码将完成这项工作[这是我的方式,测试和工作]

Sub compare10()

Dim found As Integer

Worksheets("orange book data alerts").Activate
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Worksheets("total orange book data alerts").Activate
With ActiveSheet
lastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Worksheets("orange book data alerts").Activate

For i = 1 To lastRow
    found = 0
    For j = 1 To lastRow2
        If Sheets("orange book data alerts").Cells(i, 1).Value = Sheets("total orange book data alerts").Cells(j, 1).Value Then
            found = 1
            For k = 1 To 13
                If Sheets("orange book data alerts").Cells(i, k).Value <> Sheets("total orange book data alerts").Cells(j, k).Value Then
                    Sheets("orange book data alerts").Cells(i, 14).Value = "Update"
                End If
            Next k
        End If
    Next j
    If found = 0 Then
        Sheets("orange book data alerts").Cells(i, 14).Value = "New Addition"
    End If
Next i

End Sub