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 said:
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!
creating financial statements
