|
早点说出你表格变动状态早解决了,用这个吧- Sub 排序()
- Dim arr1, arr2, arr3
- Dim i As Long, j As Long, k As Long, n As Long, m As Long
- Dim Co As Long
- '设置自定义排序的顺序
- arr1 = Array("外妇科", "手术室", "内儿科", "西医科", "中医科", "耳鼻喉科", "放射科", "检验科", "B超室", "口腔科", "针灸科", "西药房", "中药房", "收费室", "疾控科", "合管办", "后勤科")
- arr2 = Range("A1:N1")
- For i = 1 To UBound(arr2, 2)
- If arr2(1, i) = "科室" Then
- Co = i
- Exit For
- End If
- Next i
- n = ActiveCell.Row '获取活动单元格行号
- m = [A65536].End(xlUp).Row
- If n = 1 Or n > m Then
- MsgBox "活动行超出范围,不能排序"
- Exit Sub
- End If
- arr2 = Range(Cells(n, 1), Cells(m, "N")) '活动单元格至最后一行数据赋值给数组arr2
- ReDim arr3(1 To UBound(arr2), 1 To UBound(arr2, 2))
- m = 0 '计数变量
- For i = 0 To UBound(arr1)
- For j = 1 To UBound(arr2)
- If arr1(i) = arr2(j, Co) Then
- m = m + 1
- For k = 1 To UBound(arr2, 2)
- arr3(m, k) = arr2(j, k)
- Next k
- End If
- Next j
- Next i
- Cells(n, 1).Resize(UBound(arr3), UBound(arr3, 2)) = arr3
- MsgBox "排序完成"
- End Sub
复制代码 |
|