Finding a value quickly using VBA
 

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
[code]
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

--
-regards

Finding a value quickly using VBA
 

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

"GeorgeJ" wrote:

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
[code]
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

--
-regards

Finding a value quickly using VBA
 

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:

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
[code]
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

--
-regards

Finding a value quickly using VBA
 

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

On Jul 15, 6:30 pm, GeorgeJ wrote:
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
[code]
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

--
-regards

Finding a value quickly using VBA
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"GeorgeJ" wrote in message
...
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
[code]
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

--
-regards