|
发表于 2020-9-17 09:02
|
显示全部楼层
本楼为最佳答案
修改前
Sub chazhao()
Dim wb As Workbook, last%, arr, x%, y%, d, s, a As Date, b As Date
Application.ScreenUpdating = False
a = Time
Set wb = ThisWorkbook
Set d = CreateObject("scripting.dictionary")
last = wb.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
With wb.Sheets(1)
For i = 2 To last
For j = 2 To 9
d.Add .Cells(i, j).Value, j
Next j
For j = 2 To 9
For m = i - 1 To 1 Step -1
s = .Cells(m, j)
If d.Exists(s) Then
.Cells(i, j + 8) = i - m
Exit For
End If
Next m
If .Cells(i, j + 8) = "" Then .Cells(i, j + 8) = 0
Next j
d.RemoveAll
Next i
End With
b = Time - a
Application.ScreenUpdating = False
MsgBox "查找完毕,用时:" & b
End Sub
其他老师修改后:
Sub tst()
Dim arr, brr, d As Object
Range("J1:Q" & [A65536].End(xlUp).Row + 1).ClearContents
Set d = CreateObject("scripting.dictionary")
arr = Range("A1").CurrentRegion
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2) - 1)
For i = 2 To UBound(arr)
For j = 2 To UBound(arr, 2)
d(arr(i, j)) = ""
Next
For j = 2 To UBound(arr, 2)
For k = i - 1 To 1 Step -1
If d.exists(arr(k, j)) Then
brr(i, j - 1) = i - k
Exit For
else
brr(i, j - 1) =0
End If
Next
Next
d.RemoveAll
Next
[J1].Resize(UBound(brr), UBound(brr, 2)) = brr
End Sub
|
评分
-
查看全部评分
|