|
发表于 2013-10-31 15:55
|
显示全部楼层
本楼为最佳答案
- Sub df()
- Dim D(1) As New Dictionary, Arr(1), K&, U&
- Dim Ar(1 To 1000, 1 To 3), ArErr$(), strErr$
- strErr = "此记录姓名可能录入有误,请核实!"
- Arr(0) = Sheet1.Range("A1").CurrentRegion.Value
- Arr(1) = Sheet2.Range("A1").CurrentRegion.Value
- ReDim ArErr(1 To UBound(Arr(1)), 1 To 1)
- For i = 2 To UBound(Arr(0))
- D(0).Add Arr(0)(i, 2), i
- Next i
- For i = 2 To UBound(Arr(1))
- If D(0).Exists(Arr(1)(i, 2)) Then
- If Arr(1)(i, 1) <> Arr(0)(D(0)(Arr(1)(i, 2)), 1) Then
- ArErr(i, 1) = strErr
- End If
- D(1)(Arr(1)(i, 2)) = D(1)(Arr(1)(i, 2)) + Arr(1)(i, 3)
- End If
- Next i
- Arr(1) = D(0).Keys
- For i = 0 To UBound(Arr(1))
- K = D(0)(Arr(1)(i))
- If D(1).Exists(Arr(1)(i)) Then
- If Arr(0)(K, 3) <> D(1)(Arr(1)(i)) Then
- U = U + 1
- Ar(U, 1) = Arr(0)(K, 1)
- Ar(U, 2) = Arr(0)(K, 2)
- Ar(U, 3) = Arr(0)(K, 3) - D(1)(Arr(1)(i))
- End If
- Else
- U = U + 1
- Ar(U, 1) = Arr(0)(K, 1)
- Ar(U, 2) = Arr(0)(K, 2)
- Ar(U, 3) = Arr(0)(K, 3)
- End If
- Next i
- Sheet2.[d1].Resize(UBound(ArErr)) = ArErr
- Sheet3.[a2].Resize(U, 3) = Ar
- End Sub
复制代码 看下是否为附件效果?
求解.zip
(12.2 KB, 下载次数: 12)
|
|