|
发表于 2016-6-18 16:50
|
显示全部楼层
本楼为最佳答案
lidayu 发表于 2016-6-18 16:34
老司机带带我 您好,就是要这样效果。我突然想能不能把P、Q列并在P列用(数量-行号),烦请您再帮我改下 ... - Sub xx()
- Dim d, i&, j&, n&, arr, brr(), x&, crr, temp&, drr()
- Set d = CreateObject("Scripting.Dictionary")
- With Sheet1
- n = .Cells(.Rows.Count, 8).End(xlUp).Row
- arr = .Range("H4:J" & n)
- For i = 1 To n - 3
- 'ReDim Preserve brr(1 To 2, 1 To i)
- If arr(i, 1) < arr(i, 2) Then
- temp = arr(i, 1)
- arr(i, 1) = arr(i, 2)
- arr(i, 2) = temp
- End If
- If Not d.Exists(arr(i, 1) & "_" & arr(i, 2)) Then
- d.Add arr(i, 1) & "_" & arr(i, 2), i + 3
- Else
- d(arr(i, 1) & "_" & arr(i, 2)) = d(arr(i, 1) & "_" & arr(i, 2)) & "," & i + 3
- End If
- Next
- For i = 1 To n - 3
- ReDim Preserve brr(1 To 2, 1 To i)
- ReDim Preserve drr(1 To i)
- If InStr(d(arr(i, 1) & "_" & arr(i, 2)), ",") Then
- crr = Split(d(arr(i, 1) & "_" & arr(i, 2)), ",")
- For j = 0 To UBound(crr)
- brr(1, i) = brr(1, i) + arr(crr(j) - 3, 3)
- Next
- brr(2, i) = d(arr(i, 1) & "_" & arr(i, 2))
- d.Remove (arr(i, 1) & "_" & arr(i, 2))
- drr(i) = brr(1, i) & "-" & brr(2, 1)
- End If
- Next
- .Range("P4:P" & n) = Application.WorksheetFunction.Transpose(drr)
- End With
- End Sub
复制代码 |
评分
-
查看全部评分
|