|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
'如果姓名有重复结果就变得不确定,最好用ID等唯一值来作为字典键,,,
Option Explicit
Sub test()
Dim arr(2), i, j, m, mark
mark = "调离、退休"
ReDim dic(UBound(arr))
For i = 1 To UBound(dic)
Set dic(i) = CreateObject("scripting.dictionary")
arr(i) = Sheets(Format(i, "00")).[a1].CurrentRegion
For j = 2 To UBound(arr(i))
If InStr(mark, arr(i)(j, 2)) = 0 Then dic(i)(arr(i)(j, 3)) = i
Next
Next
For i = 2 To UBound(arr(1))
If Not dic(2).exists(arr(1)(i, 3)) And InStr(mark, arr(1)(i, 2)) = 0 Then
m = m + 1
For j = 1 To UBound(arr(1), 2)
arr(1)(m, j) = arr(1)(i, j)
Next
End If
With Sheets("减员").[a2]
.Resize(Rows.Count - 1, UBound(arr(1), 2)).ClearContents
If m > 0 Then .Resize(m, UBound(arr(1), 2)) = arr(1)
End With
Next
m = 0
For i = 2 To UBound(arr(2))
If Not dic(1).exists(arr(2)(i, 3)) And InStr(mark, arr(2)(i, 2)) = 0 Then
m = m + 1
For j = 1 To UBound(arr(2), 2)
arr(2)(m, j) = arr(2)(i, j)
Next
End If
With Sheets("增员").[a2]
.Resize(Rows.Count - 1, UBound(arr(2), 2)).ClearContents
If m > 0 Then .Resize(m, UBound(arr(2), 2)) = arr(2)
End With
Next
End Sub |
评分
-
3
查看全部评分
-
|