Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Greater than or equal to | Excel Worksheet Functions | |||
Greater/Less Than or Equal To | Excel Discussion (Misc queries) | |||
IF with Equal to or Greater than | Excel Discussion (Misc queries) | |||
Greater Than/Equal To Formula | Excel Discussion (Misc queries) | |||
Vlookup but also equal to and greater than? | Excel Worksheet Functions |