Finding a value quickly using VBA

[Guest]

Lets assume I have a column of numbers sorted in ascending order. Suppose
these numbers occupy the range B1:B1000. Suppose I want VBA code for

Function FindNum(Num as Long) as Long

End Function

which returns 0 if Num is not in column B and if it is returns the first row
in which Num  is located.  I realize I could write a loop to do this but is
there a faster, non-loop method?  What about the case in which the numbers in
col B are not sorted?

Thanks

 

[Guest]

try:

=FINDNUM(B1:B1000,123)



Function FindNum(ByRef rng As Range, Num As Long) As Long
res = Application.Match(Num, rng, 0)
If IsError(res) Then
FindNum = 0
Else
FindNum = res + rng(1).Row - 1
End If

End Function

 

[Guest]

try: this is looking for 101

Sub Where()
On Error GoTo noMatch
MsgBox ("Row: ") & Range("B1:B1000").Find(101, LookIn:=xlValues,
lookat:=xlWhole).Row
End
noMatch:
MsgBox ("No match")
End Sub


"GeorgeJ" skrev:

 

[Pete_UK]

If the numbers are sorted then you could use a binary search method -
on average with 1000 numbers, it would only need to look at 10 to
determine if the item is in the list or not. However, if you have
duplicated numbers it will not necessarily find the first occurrence.

If the numbers are not sorted then you will have to use a sequential
search, which on average will need to look at 500 items out of a list
of 1000 to determine if the sought number is present.

Hope this helps.

Pete

 

[Don Guillett]

simple one since you have the column
Function fn(x)
On Error GoTo nono:
fn = Columns(2).Find(x, lookat:=xlWhole).Row
nono:
End Function