分段排序问题

wicke3 · 发表于 2014-4-3 07:06

本帖最后由 wicke3 于 2014-4-3 07:23 编辑

分段排序

现有多个段落单元格，希望这几段进行排序。其中段落中的内容保持不变，参考为每段开始第二个的单元格（如图中黄色阴影部分，按数字大小比较），且位置保持与原段落起始行相同。该如何编写这个段落，还请大家多多指点

排序.rar (6.92 KB, 下载次数: 18)

liulang0808 · 发表于 2014-4-3 07:53

Private Sub CommandButton1_Click()
Dim arr1(), arr2()
a = 0
On Error Resume Next
If Cells(1, 2) <> "" Then
      ReDim Preserve arr1(1 To 1)
      ReDim Preserve arr2(1 To 1)
      arr1(1) = Cells(1, 2)
      arr2(1) = 1

      a = 1
End If
For j = 2 To Cells(Rows.Count, 2).End(3).Row
      If Cells(j - 1, 2) = "" And Cells(j, 2) <> "" Then
         a = a + 1
         ReDim Preserve arr1(1 To a)
         ReDim Preserve arr2(1 To a)
         arr1(a) = Cells(j, 2)
         arr2(a) = j
      End If
Next j
m = WorksheetFunction.Max(arr1) + 1
l1 = 1
For j = 1 To UBound(arr1)

         If WorksheetFunction.Min(arr1) = m Then
            Exit Sub
         Else
            For i = 1 To UBound(arr1)
                  If arr1(i) = WorksheetFunction.Min(arr1) Then
                     arr1(i) = m
                     Cells(arr2(i), 2).CurrentRegion.Copy Cells(arr2(l1), "i")
                     l1 = l1 + 1
                  End If
            Next i
         End If

Next j
End Sub

liulang0808 · 发表于 2014-4-3 07:55

xiangjianfujaineirong

wicke3 · 发表于 2014-4-3 08:05

liulang0808 发表于 2014-4-3 07:55
xiangjianfujaineirong

非常感谢，你太牛了

chxw68 · 发表于 2014-4-3 08:15

Sub test()
Dim d As Object
Dim d1 As Object
Dim r%, i%
Dim arr, brr(), crr()
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
With Worksheets("sheet1")
r = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range("a1:f" & r)
End With
k = 0
i = 1
Do While i < r
Do While Len(arr(i, 1)) = 0
i = i + 1
Loop
k = k + 1
m = 1
Do While Len(arr(i, 1)) <> 0
If Not d.Exists(k) Then
m = 1
d1(k) = i
Else
m = UBound(d(k), 2) + 1
End If
ReDim Preserve brr(1 To 6, 1 To m)
For j = 1 To 6
brr(j, m) = arr(i, j)
Next
d(k) = brr
i = i + 1
If i > r Then Exit Do
Loop
Loop
kk = d.Keys
tt1 = d1.Items
ReDim crr(0 To UBound(kk))
For i = 0 To UBound(kk)
crr(i) = d(kk(i))(2, 1)
Next
n = UBound(kk)
For i = 0 To n - 1
p = i
For j = i + 1 To n
If crr(p) > crr(j) Then
p = j
End If
Next
If p <> i Then
temp = crr(i): crr(i) = crr(p): crr(p) = temp
temp = kk(i): kk(i) = kk(p): kk(p) = temp
End If
Next
For i = 0 To UBound(kk)
xm = kk(i)
Cells(tt1(i), 13).Resize(UBound(d(xm), 2), UBound(d(xm))) = Application.Transpose(d(xm))
Next
End Sub

复制代码

chxw68 · 发表于 2014-4-3 08:16

参见附件。

Zamyi · 发表于 2014-4-3 09:07

“且位置保持与原段落起始行相同”如果某段落大于其他段落，可能造成超出其他段落，改为按段落长度比较合理。

Sub test()
n = [b65536].End(3).Row + 4'最后段落加4行
r = Range("b1:b" & n)
t = r(1, 1)
For i = 1 To n
If r(i, 1) = "" Then b = True
If b Then If r(i, 1) <> "" Then t = r(i, 1): b = False
r(i, 1) = t
Next
[e1].Resize(n) = r
Range("a1:e" & n).Sort [e1]
[e:e] = ""
End Sub

复制代码

excel002009 · 发表于 2014-4-3 14:23

虽然，已经有这么多人回复了，我再提供个吧，见证我在论坛的进步。（zamyi大侠的考虑我也想过，但是我并没按想的做，是按楼主要求做的。）

我的.zip (22.73 KB, 下载次数: 20)

excel002009 · 发表于 2014-4-3 15:57

这次上传个修改了丁点的（模块3里的），模块2、模块3里的代码是我的。

我的00.zip (26.47 KB, 下载次数: 23)

wicke3 · 发表于 2014-4-3 23:25

非常感谢诸位

		自动登录	找回密码
密码			免费注册

[求助] 分段排序问题

点评