程序说明:在工作表中选择一列数据,然后选取列表中选区的任一单元格后,消息对话框显示出该单元格数值在选区中的排序位置。按输入框提示进行操作,默认为当前选中的列。
程序清单如下:Option Explicit ‘进行变量声明Dim MyCell As RangeDim r As IntegerDim MyRange As RangeDim Ans
Sub rankalist() Dim m As Integer Set MyRange = Selection On Error Resume Next
m = Selection.Count MsgBox "Selection has " & m & " cells.", vbInformation, "Selection Count"
Call rankprocess ‘调用子过程 While Ans = vbYes Call rankprocess Wend While Ans = vbNo Exit Sub WendEnd Sub
Sub rankprocess() Set MyCell = Application.InputBox(prompt:="Please select a cell:", Title:="Cell", Type:=8) ‘用输入框返回一个单元格对象给MyCell对象变量 If Union(MyCell, MyRange).Address = MyRange.Address Then ‘判断单元格是否在选区内 r = 1 + MyRange.Cells.Count - Application.WorksheetFunction.rank(MyCell.Value, MyRange, 0) ‘使用Excel的rank函数进行排序 Ans = MsgBox(" the present cell is ranked " & r & " in the list " & vbNewLine & "Continue?", vbYesNo) ‘显示排序结果并询问是否继续查看其它单元格排序,还是退出 Else MsgBox "Please select a cell in selection." End IfEnd SubUploadFiles/2006-5/516295031.rar