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

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

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

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

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

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


"Factivator" wrote in message
...
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.

I tried the easy way of changing "Match" in your code to "Find", but it
dit not work.


Morten

"Charles Williams" wrote:

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

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

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

"Factivator" wrote in message
...
Hi Tom,

Well, well, what do you know; here I have been crawling all over the net
for
several days trying to fix the "Find" problems in my UDF.

The solution you suggested works fine, combined with Charles' original
help.
Thanks a lot to the both of you !


Morten

"Tom Ogilvy" wrote:

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


"Factivator" wrote in message
...
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.

I tried the easy way of changing "Match" in your code to "Find", but
it
dit not work.


Morten

"Charles Williams" wrote:

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