Is there a simple formula for getting cell count beween a certain
value? Example:
In column A1:A8 I have the following numbers.
1
3
6
9
6
7
3
2
I would like a formula that can give me the cell count between
A2(number3) and A7(number 3), which would be 4.
How about a User Defined Function?
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=MaxBetweenNum(rng,Nbr)
where Rng is the cell range to search, and Nbr is the number you are searching for.
As per your example, the result is the maximum row count "between" the duplicate Nbr's (excluding from the count both the rows where Nbr actually exists).
Also, if Nbr is not a duplicate (or is non-existent), the function will return a #NUM! error.
===============================================
Option Explicit
Function MaxBetweenNum(rg As Range, Nbr As Long) As Variant
Dim c As Range
Dim sFirstAddress As String
Dim bFirstRun As Boolean
Dim i As Long, j As Long
With rg
Set c = .Find(what:=Nbr, after:=rg(.Rows.Count), _
LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext)
If Not c Is Nothing Then
i = 0
j = c.Row
sFirstAddress = c.Address
Do 'FindNext doesn't seem to work in a Function
Set c = .Find(what:=Nbr, after:=c)
If c.Address <> sFirstAddress Then
i = WorksheetFunction.Max(i, c.Row - j)
j = c.Row
End If
Loop Until c.Address = sFirstAddress
End If
End With
MaxBetweenNum = i - 1
If i = 0 Then MaxBetweenNum = CVErr(xlErrNum)
End Function
========================================
