Selection.Find a value Greater Or Equal
 

Is it possible to use other logical operands such as Greater_Or_Equal in a
Selection.Find statement? If not, is there another way to find the first
Greater_Or_Equal cell in a sorted range?

My search range is currently 12000 cells and growing so I prefer not to
search by code, if possible...

Thanks in advance

Selection.Find a value Greater Or Equal
 

Nope. Find looks for specific values. What you could do since the list is
sorted is to write your own simple search routine by determining the mid
point of the list and then doing a greater than / less than comparison to
that value. This will cut the list in half. Now bisect (this is know as the
method of bisection) that half again and determine if the mid point is
greater / less than. With a fairly simple do until loop you should be able to
get this to work down to your final value very quickly...
--
HTH...

Jim Thomlinson


"DoctorG" wrote:

Is it possible to use other logical operands such as Greater_Or_Equal in a
Selection.Find statement? If not, is there another way to find the first
Greater_Or_Equal cell in a sorted range?

My search range is currently 12000 cells and growing so I prefer not to
search by code, if possible...

Thanks in advance

Selection.Find a value Greater Or Equal
 

Sub GreaterThanOrEqualTo()
Dim res As Variant
res = Application.Match(20, Range("A1:A12000"), 1)
If Cells(res, 1) = 20 Then
Cells(res, 1).Select
Else
Cells(res + 1, 1).Select
End If
End Sub

--
Regards,
Tom Ogilvy


"DoctorG" wrote:

Is it possible to use other logical operands such as Greater_Or_Equal in a
Selection.Find statement? If not, is there another way to find the first
Greater_Or_Equal cell in a sorted range?

My search range is currently 12000 cells and growing so I prefer not to
search by code, if possible...

Thanks in advance

Selection.Find a value Greater Or Equal
 

Thank you both for your quick replies!!! You're great!

"DoctorG" wrote:

Is it possible to use other logical operands such as Greater_Or_Equal in a
Selection.Find statement? If not, is there another way to find the first
Greater_Or_Equal cell in a sorted range?

My search range is currently 12000 cells and growing so I prefer not to
search by code, if possible...

Thanks in advance

Selection.Find a value Greater Or Equal
 

I followed Tom's suggestion and created the following procedure which also
checks for Beginning and EndOfRange (remember Clipper's BOF() and EOF() ??).
The code works great.

The thing is I nedd to use it to search dates. I found that only when
searching dates formatted as numbers I am able to MATCH correctly, because
formatted dates fall out of sequence (unless they are ANSI dates but who uses
them). If only there was a way to avoid formatting the range back and
forth... Can you suggest anything?? Is there a way to MATCH against the
underlying date number and not the visible date format?

Private Sub test()

Dim res As Variant

aaa = "10/1/06"
bbb = Val(Format(aaa, "#"))
cRange = "a5:a22"
Range(cRange).NumberFormat = "#"

nFirstRow = Range(cRange).Row
nLastRow = Range(cRange).Rows.Count + nFirstRow - 1
nColumn = Range(cRange).Column

res = Application.Match(bbb, Range(cRange), 0) ' search for exact match '
If Not IsError(res) Then
Cells(res + nFirstRow - 1, nColumn).Select ' compensate for first line
offset '
MsgBox ("Exact match")
Else
res = Application.Match(bbb, Range(cRange), 2) ' search for greater '
If Not IsError(res) Then
Cells(res + nFirstRow, nColumn).Select
If ActiveCell.Row nLastRow Then ' search value is greater than any
value in range'
ActiveCell.Offset(-1, 0).Select
MsgBox ("End of range reached - Positioning active cell to last row")
Else
MsgBox ("Greater value found")
End If
Else ' value is less than any value in range '
Cells(nFirstRow, nColumn).Select
MsgBox ("Beginning of range reached - Positioning active cell to first
row")
End If

End If

Range(cRange).NumberFormat = "ddd dd/mm/yy"

End Sub

Selection.Find a value Greater Or Equal
 

Tom,

I resurrected this three year old thread because it closely resembles my
problem, except I need to select a range up to when the value is greater than
0.