|
<续> 问题24：如何查找两个值之间的值？
解答：在Excel和大多数的MS Office应用程序中，有一个“查找”功能可用来在一个范围、工作表或工作簿中查找特定的值、或者文本字符串。然而，没有一个用于查找在两个值之间(指定的最大值和最小值)之间第一次出现某个值的位置的功能，我们能使用VBA代码来处理。代码如下：
‘***********************************
Sub GetBetween()
Dim strNum As String
Dim lMin As Long, lMax As Long
Dim rFound As Range, rLookin As Range
Dim lFound As Long, rStart As Range
Dim rCcells As Range, rFcells As Range
Dim lCellCount As Long, lcount As Long
Dim bNoFind As Boolean strNum = InputBox("请先输入最大值,然后输入逗号," _
& "接着输入最大值" & vbNewLine & _
vbNewLine & "例如: 1,10", "输入最小值和最大值")
If strNum = vbNullString Then Exit Sub
On Error Resume Next
lMin = Left(strNum, InStr(1, strNum, ","))
If Not IsNumeric(lMin) Or lMin = 0 Then
MsgBox "输入数据错误, 或者最小值不应为零", vbCritical
Exit Sub
End If
lMax = Replace(strNum, lMin & ",", "")
If Not IsNumeric(lMax) Or lMax = 0 Then
MsgBox "输入数据错误,或者最大值不应为零", vbCritical
Exit Sub
End If
If lMax < lMin Then
MsgBox "最小值大于最大值", vbCritical
Exit Sub
End If
If lMin + 1 = lMax Then
MsgBox "最大值和最小值之间没有范围", vbCritical
Exit Sub
End If
If Selection.Cells.Count = 1 Then
Set rCcells = Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
Set rFcells = Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
Set rStart = Cells(1, 1)
Else
Set rCcells = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
Set rFcells = Selection.SpecialCells(xlCellTypeFormulas, xlNumbers)
Set rStart = Selection.Cells(1, 1)
End If
'缩小查找范围
If rCcells Is Nothing And rFcells Is Nothing Then
MsgBox "工作表无数据", vbCritical
Exit Sub
ElseIf rCcells Is Nothing Then
Set rLookin = rFcells.Cells '公式
ElseIf rFcells Is Nothing Then
Set rLookin = rCcells.Cells '常量
Else
Set rLookin = Application.Union(rFcells, rCcells) '公式和常量
End If
lCellCount = rLookin.Cells.Count
Do Until lFound > lMin And lFound < lMax And lFound > 0
lFound = 0
Set rStart = rLookin.Cells.Find(What:="*", After:=rStart, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
lFound = rStart.Value
lcount = lcount + 1
If lCellCount = lcount Then
bNoFind = True
Exit Do
End If
Loop
rStart.Select
If bNoFind = True Then
MsgBox "没有数据在" _
& lMin & " 和 " & lMax & "之间", vbInformation
End If
On Error GoTo 0
End Sub
‘***********************************
该代码将以工作表中“查找”功能相同的方式工作，当仅选择一个单元格时，将在所有单元格中查找；当选择一部分单元格时，仅在所选单元格区域中查找，在两个值之间的符合条件的第一个单元格被选中，不包含最小值和最大值本身。注意，本程序代码不会查找零值。
例如，在工作表中有1至10共10个数据，若您要查找3至5之间的数据，运行后在对话框中输入3,5，内容为4的单元格将被选中。
示例文档见(问题24)查找最大最小值之间的值.xls。
By fanjy in 2006-8-3
| 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2006-8-3 15:30
楼主
