|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
看来老师们有事较忙,我来把更正解释的补上
Sub test2()
Dim arr, brr, crr, arr2, brr2, crr2, i&, j&, i2&, i3&, j2&, k, kk, s, p, p2, n, judge As Boolean, maxt, d As Object
Set d = CreateObject("Scripting.Dictionary")
arr = Sheets("用药收集表").Range("a2:e" & Cells(Rows.Count, 1).End(xlUp).Row)
ReDim brr(1 To UBound(arr) / 3, 1 To 5)
For i = 1 To UBound(arr)
If Not d.exists(arr(i, 1)) Then
k = k + 1
d(arr(i, 1)) = k
brr(d(arr(i, 1)), 1) = arr(i, 1)
brr(d(arr(i, 1)), 2) = 1
brr(d(arr(i, 1)), 3) = i
Else
If brr(d(arr(i, 1)), 2) + 2 = UBound(brr, 2) Then
ReDim Preserve brr(1 To UBound(arr) / 3, 1 To brr(d(arr(i, 1)), 2) + 3)
End If
brr(d(arr(i, 1)), 2) = brr(d(arr(i, 1)), 2) + 1
brr(d(arr(i, 1)), 4 + brr(d(arr(i, 1)), 2) - 2) = i
If brr(d(arr(i, 1)), 2) > brr(k - 1, 2) Then maxt = brr(d(arr(i, 1)), 2)
End If
Next
brr2 = Range("j2:j" & [a2].End(xlDown).Row).Value
ReDim crr(1 To k, 1 To UBound(brr, 2) + maxt)
For i = 1 To UBound(brr2)
s = brr2(i, 1)
If d.exists(s) Then
kk = kk + 1
crr(kk, 1) = brr(d(s), 1)
crr(kk, 4) = UBound(arr) - brr(d(s), brr(d(s), 2) + 2)
ReDim arr2(1 To brr(d(s), 2) - 1)
ReDim crr2(1 To 4)
For j = brr(d(s), 2) + 2 To 4 Step -1
arr2(brr(d(s), 2) + 2 - j + 1) = brr(d(s), j) - brr(d(s), j - 1) - 1
If Len(crr(kk, 2)) = 0 Then
crr(kk, 2) = arr2(brr(d(s), 2) + 2 - j + 1)
ElseIf Len(crr(kk, 2)) And arr2(brr(d(s), 2) + 2 - j + 1) > crr(kk, 2) Then
crr(kk, 2) = arr2(brr(d(s), 2) + 2 - j + 1)
End If
If arr2(brr(d(s), 2) + 2 - j + 1) = 0 Then
crr(kk, brr(d(s), 2) + 2 - j + 10) = 1
Else
crr(kk, brr(d(s), 2) + 2 - j + 10) = 2
End If
Next
If crr(kk, 4) > crr(kk, 2) Then crr(kk, 2) = crr(kk, 4)
crr(kk, 3) = arr2(1)
judge = False
For j2 = 1 To UBound(arr2)
If arr2(j2) = 0 And judge = False Then
crr2(1) = crr2(1) + 1
If arr2(j2 + 1) > 0 Then judge = True
ElseIf arr2(j2) > 0 And judge = False Then
crr2(2) = crr2(2) + 1
ElseIf arr2(j2) = 0 And judge = True Then
crr2(3) = crr2(3) + 1
ElseIf arr2(j2) > 0 And judge = True Then
crr2(4) = crr2(4) + 1
End If
Next
If crr2(1) > 0 And crr2(1) >= crr2(3) Then
crr(kk, 5) = crr2(1) + 1
ElseIf crr2(1) > 0 And crr2(1) < crr2(3) Then
crr(kk, 5) = crr2(3) + 1
Else
crr(kk, 5) = 0
End If
If crr2(2) > 0 And crr2(1) > 0 Then
crr(kk, 7) = crr2(2)
Else
crr(kk, 7) = 0
End If
If crr2(1) > 0 And (crr2(4) > 1 And crr2(3) > 0) Then
crr(kk, 6) = crr2(4) - 1
ElseIf crr2(1) > 0 And (crr2(4) = 1 Or crr2(3) = Empty) Then
crr(kk, 6) = crr2(4)
Else
crr(kk, 6) = 0
End If
End If
Next
Sheets("用药收集表").Range("j2").Resize(kk, 7) = crr
End Sub
|
|