|
yjwdjfqb 发表于 2012-2-7 08:47
这样行不呢!
就是排序完成后,从“序号”下一个单元格从1开始编写序号,编写序号的终止单元格是,当前 ...
- Sub 科室排序()
- Dim arr, i&, j&, c%, r&
- '设置自定义排序的顺序
- arr = Array("外妇科", "手术室", "内儿科", "西医科", "中医科", "耳鼻喉科", "放射科", "检验科", "B超室", "口腔科", "针灸科", "西药房", "中药房", "收费室", "疾控科", "合管办", "后勤科")
- If Not Cells.Find("序号") Is Nothing Then
- Set Rng = Cells.Find("序号").CurrentRegion
- If Not Intersect(Rng, ActiveCell) Is Nothing Then
- With Application
- .ScreenUpdating = False
- .AddCustomList ListArray:=arr
- i = .GetCustomListNum(arr) '返回字符串数组的自定义序列号
- c = Rng.Item(1, 1).End(xlToRight).Column '数据源最右侧列号
- r = Cells(65536, ActiveCell.Column).End(xlUp).Row '所选单元格所在列最后一个数据的行号
-
- '排序区域
- With Range(Cells(ActiveCell.Row, Rng.Column), Cells(r, c))
- '设置排序的起始单元格
- .Sort key1:=ActiveCell, order1:=xlAscending, _
- Header:=xlNo, OrderCustom:=i + 1
- End With
-
- '重写序号
- For i = ActiveCell.Row + 1 To r '这句不清楚你是否要+1,你自己修改吧
- j = j + 1
- Cells(i, 1) = j
- Next i
-
- .DeleteCustomList ListNum:=i
- .ScreenUpdating = True
- MsgBox "排序完成", vbInformation
- End With
- End If
- End If
- End Sub
复制代码
附件8.rar
(21.14 KB, 下载次数: 20)
|
|