ExcelBanter - 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