MS-Excel / General Formatting

---

Find a Number Between Two Numbers

Enhance Excel's Find feature to search for the first occurrence of a number that falls between two specified numbers.

Like all Microsoft Office applications, Excel has a Find feature to help locate a specified value or text string in a range, worksheet, or workbook. However, no such feature exists to find the first occurrence of a number that is between an arbitrary minimum and a maximum number. This tutorial provides some Excel VBA macro code to do just that.

Many people would go with a loop to get a number between a nominated range, but that can be extremely slowand horribly inefficient if the worksheet contain thousands of used cells. The method in this tutorial makes use of the SpecialCells method to check numeric cells only.

The code works in the same way as the standard Find feature does. That is, it searches all cells on the worksheet if only a single cell is selected or only the selected cells if more than one cell is selected.

It searches by rows, locating and selecting the first cell that has a value between (not equal to) the specified minimum and maximum.

The Code

Right-click on the sheet tab of the worksheet and select View Code → Insert → Module. In the new module, paste the following code:

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("Please enter the lowest value, then a
    comma, " _
	& "followed by the highest value" & vbNewLine & _
	vbNewLine & "E.g. 1,10", "GET BETWEEN")

	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 "Error in your entering of numbers, or Min
            was a zero", 
	    vbCritical, "Ozgrid.com"
	    Exit Sub
	End If

	lMax = Replace(strNum, lMin & ",", "")
	If Not IsNumeric(lMax) Or lMax = 0 Then

	    MsgBox "Error in your entering of numbers, or Max
            was a zero", 
	    vbCritical, "Ozgrid.com"
	    Exit Sub
	End If

	If lMax < lMin Then
	    MsgBox "Min is greater than Max", vbCritical,
            "Ozgrid.com"
	    Exit Sub
	End If

	If lMin + 1 = lMax Then
	    MsgBox "No scope between Min and Max", vbCritical,
            "Ozgrid.com"
	    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

	'Reduce down range to look in
	If rCcells Is Nothing And rFcells Is Nothing Then
	    MsgBox "Your Worksheet contains no numbers", vbCritical,
            "ozgrid. com"

	    Exit Sub
	ElseIf rCcells Is Nothing Then
	    Set rLookin = rFcells.Cells 'formulas
	ElseIf rFcells Is Nothing Then
	    Set rLookin = rCcells.Cells 'constants
	Else
	    Set rLookin = Application.Union(rFcells, rCcells) 'Both
	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 "No numbers between " _
	    & lMin & " and " & lMax, vbInformation, "Ozgrid.com"
	End If
	On Error GoTo 0
End Sub

Close the VBE, return to Excel proper, and save your workbook.

To run the code, press Alt/Option-F8, select the macro, and press Run. The GETBETWEEN dialog will pop up and ask you to "Please enter the lowest value, then a comma, followed by the highest value." Enter 1,10 and click OK. The number 2 will be highlighted because the number 2 is the first cell that has a value between (not equal to) the specified minimum (1) and maximum (10).

This code will not locate a zero value.