Problem with "On error resume next" with "custom VLookup"

[Guest]

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(Val1, 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

 

[Charles Williams]

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

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(Val1, 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.

 

[Tom Ogilvy]

Find does not work in a User Defined Function in Excel 2000 and earlier. I
believe Dave Peterson said it works in Excel 2002 and later.

Match will work with a wildcard

=Match("*def*",A1:A100,0)

as an example.

sTarget = "*" & searchString & "*"

res = Application.Match(sTarget,rng,0)

--
Regards,
Tom Ogilvy

Hi Charles,

Thanks for the hint; the problem is, however, that I need the "Find"

(which you substituted with a "Match") as the idea of creating the UDF
(instead of using VLOOKUP) is that I need to be able to find instances of
Val1 in Table, where Val1 constitutes only a part of the value of the 1st
column in Table. It should, for example, find Val1="def" if there is a
record/row in Table which has "abcdefg" in the first column.

 

[Charles Williams]

If thats all you wanted to do then you dont need a UDF at all you can use
INDEX and MATCH
(using Tom's example of MATCH)

=INDEX("A1:B100",Match("*def*",A1:A100,0),2)

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

 

[Dave Peterson]

It works ok in xl2002. I'm not sure I've seen anyone post xl2003's behavior.