Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |