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