|
本帖最后由 sam-wang 于 2022-6-26 12:24 编辑
#2是找到54後,則本身列~往後加9列數據
如下是找到54,則往後的第10列為數據
請測試看看,謝謝
Sub test()
Dim Arr, xD, Brr, xC%, xN$, xR&, T$, n&, i&, i1&, j%
Set xD = CreateObject("Scripting.Dictionary")
With Sheets(2)
xC = .[b1]: xN = .[b2]: xR = .[b3]
End With
Arr = Sheets(1).[a1].CurrentRegion
ReDim Brr(1 To UBound(Arr), 1 To UBound(Arr, 2))
For i = 1 To UBound(Arr)
T = Arr(i, xC + 1): If T <> xN Then GoTo 95
R = i + xR: If R > UBound(Arr) Then GoTo 95
n = n + 1
For j = 1 To UBound(Arr, 2): Brr(n, j) = Arr(R, j): Next
95: Next
For i = 1 To n: For j = 2 To 10
xD(Brr(i, j) & "") = xD(Brr(i, j) & "") + 1
Next: Next
With Sheets(2)
[d2:e1000] = ""
.Range("d2:d" & xD.Count).NumberFormatLocal = "@"
.[d2].Resize(xD.Count, 1) = Application.Transpose(xD.keys)
With .Range("d2:e" & xD.Count + 1)
Arr = .Value
For i = 1 To UBound(Arr): Arr(i, 2) = xD(Arr(i, 1) & ""): Next
.NumberFormatLocal = "@"
.Value = Arr
End With
End With
End Sub
|
|