Option Explicit
Sub TEST3()
Dim ar, br, i&, j&, r&, iStart&, dic As Object, vKey, strKey$
Application.ScreenUpdating = False
iStart = Application.InputBox("起始编号", Title:="提示", Default:=101, Type:=1)
If iStart = 0 Then Exit Sub
iStart = iStart - 1
Set dic = CreateObject("Scripting.Dictionary")
r = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A2:I" & r)
.Parent.Sort.SortFields.Clear
.Sort key1:=.Item(5), Order1:=xlAscending, key2:=.Item(6), Order2:=xlAscending, Header:=xlYes
ar = .Value
For i = 2 To UBound(ar)
strKey = ar(i, 5) & ar(i, 6)
dic(strKey) = dic(strKey) & " " & i
Next i
For Each vKey In dic.keys
br = Split(dic(vKey))
For j = 1 To UBound(br)
If j Mod 20 = 1 Then iStart = iStart + 1
ar(br(j), 7) = iStart
ar(br(j), 8) = IIf(j Mod 20 = 0, 20, j Mod 20)
Next j
Next
.Value = ar
End With
Application.ScreenUpdating = True
End Sub |