数据比对

nxdx112

grf1973 发表于 2022-12-7 15:51
not in 语句中判断非空是必须的，不然什么结果都筛选不出来

虽然看不懂，感觉好简单呀。谢谢了！

lcluck2002

sdytsxd991122 发表于 2022-12-7 15:32

算法强大，代码量少，且容易看懂！

jsmyk

sdytsxd991122 发表于 2022-12-7 15:32

代码很美，简洁明了

盗版丨小艾

其实这个用PQ特别简单，合并查询

逸、

在其他大神的指导下，做出来，看一下是否正确呢。
Sub test()
Dim s1 As Range
Dim s2 As Range
Dim s3 As Range 'range("cells(2,17):cells(2,18).end(xldown)")
Dim m As Range '用于find方法中存储原表搜索姓名对象
Dim i As Range '用于find方法中存储原表搜索班级对象
Dim q As Range '用于find方法中存储新表搜索姓名对象
Dim w As Range '用于find方法中存储新表搜索班级对象
Dim n%
For Each s1 In Range("b3:b27") '应用for each循环原表新表内容，获取共有表格
    For Each s2 In Range("f2:f29")
        If s1 = s2 Then
            If s1.Offset(0, 1) = s2.Offset(0, 1) Then
                Cells((Rows.Count), 17).End(xlUp).Offset(1, 0) = s1
                Cells((Rows.Count), 18).End(xlUp).Offset(1, 0) = s1.Offset(0, 1)
            Else
            End If
         Else
         End If
    Next
Next
For n = 3 To 27 Step 1 '利用原表对比共有表，若为无则是原表独有
    Set m = Range("r3:r20").Find(Cells(n, "c"), , , xlWhole)
    Set i = Range("q3:q20").Find(Cells(n, "b"), , , xlWhole)
    If m Is Nothing And i Is Nothing Then
        Cells((Rows.Count), "k").End(xlUp).Offset(1, 0) = Cells(n, "c")
        Cells((Rows.Count), "j").End(xlUp).Offset(1, 0) = Cells(n, "b")
        Cells((Rows.Count), "i").End(xlUp).Offset(1, 0) = Cells(n, "a")
    Else
    End If
Next n

For n = 3 To 31 Step 1 '利用新表对比共有表，若为无则是新表独有
    Set q = Range("r3:r20").Find(Cells(n, "g"), , , xlWhole)
    Set w = Range("q3:q20").Find(Cells(n, "f"), , , xlWhole)
    If q Is Nothing And w Is Nothing Then
        Cells((Rows.Count), "o").End(xlUp).Offset(1, 0) = Cells(n, "g")
        Cells((Rows.Count), "n").End(xlUp).Offset(1, 0) = Cells(n, "f")
        Cells((Rows.Count), "m").End(xlUp).Offset(1, 0) = Cells(n, "e")
    Else
    End If
Next n
End Sub