|
代码如下。。。
Sub test1()
Dim sh As Worksheet, sht As Worksheet
Set d = CreateObject("scripting.dictionary")
Set sh1 = ThisWorkbook.Sheets("核对")
sh1.UsedRange.Offset(1).ClearContents
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "检核表" And sh.Name <> "核对" Then
d.RemoveAll
r = sh.Cells(Rows.Count, 1).End(3).Row
If r > 4 Then
arr = sh.Range("a5:g" & r)
For i = 1 To UBound(arr)
For j = 1 To 7
s = s & arr(i, j)
Next
If s <> "" Then d(s) = i
s = Empty
Next
For Each sht In ThisWorkbook.Worksheets
If sh.Name <> "检核表" And sh.Name <> "核对" Then
If sht.Name <> sh.Name Then
r = sht.Cells(Rows.Count, 8).End(3).Row
brr = sht.Range("h5:n" & r)
For i = 1 To UBound(brr)
s = brr(i, 3) & brr(i, 2) & brr(i, 1)
For j = 4 To 7
s = s & brr(i, j)
Next
If d.exists(s) Then d.Remove s
s = Empty
Next
End If
End If
Next
If d.Count > 0 Then
'MsgBox sh.Name & "调出没有问题!"
'Else: MsgBox sh.Name & "调出有问题!" & "有:" & d.Count & "个问题!"
For Each Item In d.items
r = sh1.Cells(Rows.Count, 1).End(3).Row + 1
For i = 1 To 7
sh1.Cells(r, i) = arr(Item, i)
Next
Next
End If
End If
End If
Next
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "检核表" And sh.Name <> "核对" Then
d.RemoveAll
r = sh.Cells(Rows.Count, 8).End(3).Row
If r > 4 Then
arr = sh.Range("h5:n" & r)
For i = 1 To UBound(arr)
s = arr(i, 3) & arr(i, 2) & arr(i, 1)
For j = 4 To 7
s = s & arr(i, j)
Next
If s <> "" Then d(s) = i
s = Empty
Next
For Each sht In ThisWorkbook.Worksheets
If sh.Name <> "检核表" And sh.Name <> "核对" Then
If sht.Name <> sh.Name Then
r = sht.Cells(Rows.Count, 1).End(3).Row
brr = sht.Range("a5:g" & r)
For i = 1 To UBound(brr)
For j = 1 To 7
s = s & brr(i, j)
Next
If d.exists(s) Then d.Remove s
s = Empty
Next
End If
End If
Next
If d.Count > 0 Then
'MsgBox sh.Name & "调入没有问题!"
'Else: MsgBox sh.Name & "调入有问题!" & "有:" & d.Count & "个问题!"
For Each Item In d.items
r = sh1.Cells(Rows.Count, 8).End(3).Row + 1
For i = 1 To 7
sh1.Cells(r, i + 7) = arr(Item, i)
Next
Next
End If
End If
End If
Next
Set d = Nothing
Beep
sh1.Activate
With sh1.UsedRange.Offset(1)
.HorizontalAlignment = xlCenter
End With
End Sub
|
评分
-
1
查看全部评分
-
|