Sub test()
arr = Sheet2.Range("a1").CurrentRegion
Set dic = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
dic(arr(i, 2)) = i
Next
arr1 = Sheet1.Range("a1").CurrentRegion
For i = 2 To UBound(arr1)
For j = 1 To UBound(arr1, 2) - 1
If dic.exists(arr1(i, 2)) Then
If arr1(i, j) <> arr(dic(arr1(i, 2)), j) Then
s = s & j
End If
End If
Next
Select Case s
Case ""
arr1(i, UBound(arr1, 2)) = "正常"
Case "4"
arr1(i, UBound(arr1, 2)) = "金额差异"
Case "3"
arr1(i, UBound(arr1, 2)) = "地区差异"
Case "34"
arr1(i, UBound(arr1, 2)) = "地区和金额"
Case "1"
arr1(i, UBound(arr1, 2)) = "日期差异"
Case "13"
arr1(i, UBound(arr1, 2)) = "日期和地区"
Case "14"
arr1(i, UBound(arr1, 2)) = "日期和金额"
Case Else
arr1(i, UBound(arr1, 2)) = "待查"
End Select
s = ""
Next
Sheet1.Range("a1").CurrentRegion = arr1
arr = Sheet1.Range("a1").CurrentRegion
Set dic = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
dic(arr(i, 2)) = i
Next
arr1 = Sheet2.Range("a1").CurrentRegion
For i = 2 To UBound(arr1)
For j = 1 To UBound(arr1, 2) - 1
If dic.exists(arr1(i, 2)) Then
If arr1(i, j) <> arr(dic(arr1(i, 2)), j) Then
s = s & j
End If
End If
Next
Select Case s
Case ""
arr1(i, UBound(arr1, 2)) = "正常"
Case "4"
arr1(i, UBound(arr1, 2)) = "金额差异"
Case "3"
arr1(i, UBound(arr1, 2)) = "地区差异"
Case "34"
arr1(i, UBound(arr1, 2)) = "地区和金额"
Case "1"
arr1(i, UBound(arr1, 2)) = "日期差异"
Case "13"
arr1(i, UBound(arr1, 2)) = "日期和地区"
Case "14"
arr1(i, UBound(arr1, 2)) = "日期和金额"
Case Else
arr1(i, UBound(arr1, 2)) = "待查"
End Select
s = ""
Next
Sheet2.Range("a1").CurrentRegion = arr1
End Sub
|