程序说明:
在工作表中选择一列数据,然后选取列表中选区的任一单元格后,消息对话框显示出该单元格数值在选区中的排序位置。按输入框提示进行操作,默认为当前选中的列。
程序清单如下:
Option Explicit ‘进行变量声明
Dim MyCell As Range
Dim r As Integer
Dim MyRange As Range
Dim 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
Wend
End 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 If
End Sub
UploadFiles/2006-5/516295031.rar