Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find cell with value greater than my number
I want to find/create a function that will sort through a column and return the cell that has a value greater than 'my number' in c. i want i = 0; while (columnC[i] < 'my number') i++; now i can use i as the index that i need. Any ideas for excel,??? Thanks a lot! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find cell with value greater than my number
Public Function HigherThanMe(rng As Range, Target As Variant) As Range
Dim cell As Range On Error GoTo ErrHandler For Each cell In rng On Error Resume Next If cell.Value Target Then Set HigherThanMe = cell Exit For End If On Error GoTo 0 Next End Function This returns a reference to a range If used in a cell =HigerthanMe(A:A,21) it would produce the value of the cell. If you want the address declare the function as String and HigherThanMe = cell.Address(0,0,External:=True) or to get the offset in the column (the index value you speak of) Public Function HigherThanMe(rng As Range, Target As Variant) As Long Dim cell As Range, Dim i a long On Error GoTo ErrHandler i = 0 For Each cell In rng On Error Resume Next i = i + 1 If cell.Value Target Then HigherThanMe = i Exit For End If On Error GoTo 0 Next End Function You could add checks to insure rng is a single, entire column. -- Regards, Tom Ogilvy heiny06 wrote in message ...[i] I want to find/create a function that will sort through a column and return the cell that has a value greater than 'my number' in c. i want i = 0; while (columnC < 'my number') i++; now i can use i as the index that i need. Any ideas for excel,??? Thanks a lot! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SMALL function to find X smallest number greater than Y | Excel Worksheet Functions | |||
How can I make a cell red if number is not greater than needed? | Excel Worksheet Functions | |||
Visual Script to find smallest number greater than 0 | Excel Discussion (Misc queries) | |||
countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
How can I get Excel to find the first number in a list greater tha | Excel Worksheet Functions |