I have this:

Function FN(Table As Range, Val1 As String)
Dim i As Integer
On Error Resume Next
For i = 1 To Table.Rows.Count
If Not WorksheetFunction.IsError(WorksheetFunction.Find(V al1, Table.Cells(i, 1), 1)) Then
FN = Table.Cells(i, 2)
End If
Next i
End Function

which always returns the value of second column in the last! row of the range
provided in the argument; if I leave out the "On error resume next" I always get #Value returned. I am sure that it only does what I tell it to do - only I don't know how to fix it.

Thanks a lot in advance !


Morten

Hi Morten,

try something like this, assuming that table is not sorted

Public Function FN(Table As Range, Val1 As String)
Dim vRow As variant

FN=cverr(XlerrNA)
On Error goto fail
vRow=application.Match(Val1, Table.columns(1), 0)
if not iserror(vrow) then
FN = Table.Cells(clng(vRow), 2)
End If
Fail:

End Function


Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"Factivator" wrote in message
...
I have this:

Function FN(Table As Range, Val1 As String)
Dim i As Integer
On Error Resume Next
For i = 1 To Table.Rows.Count
If Not WorksheetFunction.IsError(WorksheetFunction.Find(V al1,
Table.Cells(i, 1), 1)) Then
FN = Table.Cells(i, 2)
End If
Next i
End Function

which always returns the value of second column in the last! row of the
range
provided in the argument; if I leave out the "On error resume next" I
always get #Value returned. I am sure that it only does what I tell it to
do - only I don't know how to fix it.

Thanks a lot in advance !


Morten