I use the following type of function a lot:
if(iserror(vlookup(a,sheet2!$a$1:$d$15,2,0)),0,vlo okup(a,sheet2!$a$1:$d$15,2,0))
I want to replace this with:
taz(a,sheet2!$a$1:$d$15,2)
the following is my current attempt which doesnt quite work,
many thanks for any help
Function taz(a, b, c)
taz = WorksheetFunction.VLookup(a, b, c, 0)
If WorksheetFunction.IsError(taz) Then
taz = 0
Else: taz
End If
End Function--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: www.excelforum.com/member.php...oamp;userid=26479
View this thread: www.excelforum.com/showthread...hreadid=505271Function taz(a, b, c)
On Error Resume Next
taz = WorksheetFunction.VLookup(a, b, c, 0)
On Error GoTo 0
If IsError(taz) Then
taz = 0
End If
End Function
--
HTH
Bob Phillips
(remove nothere from the email address if mailing direct)
quot;T De Villiersquot; gt;
wrote in message
news:T.De.Villiers.228qdz_1138276809.0729@excelfor um-nospam.com...
gt;
gt; I use the following type of function a lot:
gt;
if(iserror(vlookup(a,sheet2!$a$1:$d$15,2,0)),0,vlo okup(a,sheet2!$a$1:$d$15,2
,0))
gt;
gt; I want to replace this with:
gt; taz(a,sheet2!$a$1:$d$15,2)
gt;
gt; the following is my current attempt which doesnt quite work,
gt; many thanks for any help
gt;
gt; Function taz(a, b, c)
gt;
gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt;
gt; If WorksheetFunction.IsError(taz) Then
gt; taz = 0
gt; Else: taz
gt; End If
gt; End Function
gt;
gt;
gt; --
gt; T De Villiers
gt; ------------------------------------------------------------------------
gt; T De Villiers's Profile:
www.excelforum.com/member.php...oamp;userid=26479
gt; View this thread: www.excelforum.com/showthread...hreadid=505271
gt;
Thanks for this, another question though, just say I want the output to
be 5
when there is an error, why doesnt the following work, many thanks:
Function taz(a, b, c)
On Error Resume Next
taz = WorksheetFunction.VLookup(a, b, c, 0)
On Error GoTo 0
If IsError(taz) Then
taz = 5
End If
End Function--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: www.excelforum.com/member.php...oamp;userid=26479
View this thread: www.excelforum.com/showthread...hreadid=505271Try this version
Function taz(a, b, c)
On Error Resume Next
taz = WorksheetFunction.VLookup(a, b, c, 0)
On Error GoTo 0
If IsEmpty(taz) Then
taz = 5
End If
End Function--
HTH
Bob Phillips
(remove nothere from the email address if mailing direct)
quot;T De Villiersquot; gt;
wrote in message
news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...
gt;
gt; Thanks for this, another question though, just say I want the output to
gt; be 5
gt; when there is an error, why doesnt the following work, many thanks:
gt;
gt; Function taz(a, b, c)
gt;
gt; On Error Resume Next
gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; On Error GoTo 0
gt; If IsError(taz) Then
gt; taz = 5
gt; End If
gt; End Function
gt;
gt;
gt; --
gt; T De Villiers
gt; ------------------------------------------------------------------------
gt; T De Villiers's Profile:
www.excelforum.com/member.php...oamp;userid=26479
gt; View this thread: www.excelforum.com/showthread...hreadid=505271
gt;
Thanks for this Bob - you're extremely helpful--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: www.excelforum.com/member.php...oamp;userid=26479
View this thread: www.excelforum.com/showthread...hreadid=505271I also use this same kind of construct often. (vlookup with error trap) I
cut and pasted the code into the quot;this workbookquot; object and used the taz
function in a worksheet but I get a #NAME error. What am I doing wrong? I
also tried putting it in the sheet objects -- same error.
My formula in B2 is: =taz(A2,Sheet1!A:B,2)
This would be very useful to me but I never thought of defining my own
function.
--
Carlos
quot;Bob Phillipsquot; gt; wrote in message
...
gt; Try this version
gt;
gt; Function taz(a, b, c)
gt;
gt; On Error Resume Next
gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; On Error GoTo 0
gt; If IsEmpty(taz) Then
gt; taz = 5
gt; End If
gt; End Function
gt;
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;T De Villiersquot;
gt;
gt; wrote in message
gt; news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...
gt; gt;
gt; gt; Thanks for this, another question though, just say I want the output to
gt; gt; be 5
gt; gt; when there is an error, why doesnt the following work, many thanks:
gt; gt;
gt; gt; Function taz(a, b, c)
gt; gt;
gt; gt; On Error Resume Next
gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; On Error GoTo 0
gt; gt; If IsError(taz) Then
gt; gt; taz = 5
gt; gt; End If
gt; gt; End Function
gt; gt;
gt; gt;
gt; gt; --
gt; gt; T De Villiers
gt; gt; ------------------------------------------------------------------------
gt; gt; T De Villiers's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26479
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=505271
gt; gt;
gt;
gt;
Put it in a normal module, not under quot;this workbookquot; or a worksheet.
--
Kevin Vaughnquot;CarlosAntennaquot; wrote:
gt; I also use this same kind of construct often. (vlookup with error trap) I
gt; cut and pasted the code into the quot;this workbookquot; object and used the taz
gt; function in a worksheet but I get a #NAME error. What am I doing wrong? I
gt; also tried putting it in the sheet objects -- same error.
gt;
gt; My formula in B2 is: =taz(A2,Sheet1!A:B,2)
gt;
gt; This would be very useful to me but I never thought of defining my own
gt; function.
gt; --
gt; Carlos
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; Try this version
gt; gt;
gt; gt; Function taz(a, b, c)
gt; gt;
gt; gt; On Error Resume Next
gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; On Error GoTo 0
gt; gt; If IsEmpty(taz) Then
gt; gt; taz = 5
gt; gt; End If
gt; gt; End Function
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;T De Villiersquot;
gt; gt;
gt; gt; wrote in message
gt; gt; news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...
gt; gt; gt;
gt; gt; gt; Thanks for this, another question though, just say I want the output to
gt; gt; gt; be 5
gt; gt; gt; when there is an error, why doesnt the following work, many thanks:
gt; gt; gt;
gt; gt; gt; Function taz(a, b, c)
gt; gt; gt;
gt; gt; gt; On Error Resume Next
gt; gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; gt; On Error GoTo 0
gt; gt; gt; If IsError(taz) Then
gt; gt; gt; taz = 5
gt; gt; gt; End If
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; T De Villiers
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; T De Villiers's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=26479
gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=505271
gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;
Thanks Kevin, that did it.
I also tried putting that module in my book.xlt so the function would be
available in new workbooks, but it doesn't appear in the new workbooks
created from book.xlt. Any ideas about that?
--
Carlos
quot;Kevin Vaughnquot; gt; wrote in message
...
gt; Put it in a normal module, not under quot;this workbookquot; or a worksheet.
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;CarlosAntennaquot; wrote:
gt;
gt; gt; I also use this same kind of construct often. (vlookup with error trap)
I
gt; gt; cut and pasted the code into the quot;this workbookquot; object and used the taz
gt; gt; function in a worksheet but I get a #NAME error. What am I doing wrong?
I
gt; gt; also tried putting it in the sheet objects -- same error.
gt; gt;
gt; gt; My formula in B2 is: =taz(A2,Sheet1!A:B,2)
gt; gt;
gt; gt; This would be very useful to me but I never thought of defining my own
gt; gt; function.
gt; gt; --
gt; gt; Carlos
gt; gt;
gt; gt; quot;Bob Phillipsquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Try this version
gt; gt; gt;
gt; gt; gt; Function taz(a, b, c)
gt; gt; gt;
gt; gt; gt; On Error Resume Next
gt; gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; gt; On Error GoTo 0
gt; gt; gt; If IsEmpty(taz) Then
gt; gt; gt; taz = 5
gt; gt; gt; End If
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;T De Villiersquot;
gt; gt; gt;
gt; gt; gt; wrote in message
gt; gt; gt; news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks for this, another question though, just say I want the output
to
gt; gt; gt; gt; be 5
gt; gt; gt; gt; when there is an error, why doesnt the following work, many thanks:
gt; gt; gt; gt;
gt; gt; gt; gt; Function taz(a, b, c)
gt; gt; gt; gt;
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt; If IsError(taz) Then
gt; gt; gt; gt; taz = 5
gt; gt; gt; gt; End If
gt; gt; gt; gt; End Function
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; T De Villiers
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; T De Villiers's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=26479
gt; gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=505271
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
Never mind.
I had more than one book.xlt and I put it in the wrong one.
--
Carlos
quot;CarlosAntennaquot; gt; wrote in message
...
gt; Thanks Kevin, that did it.
gt;
gt; I also tried putting that module in my book.xlt so the function would be
gt; available in new workbooks, but it doesn't appear in the new workbooks
gt; created from book.xlt. Any ideas about that?
gt;
gt; --
gt; Carlos
gt;
gt; quot;Kevin Vaughnquot; gt; wrote in message
gt; ...
gt; gt; Put it in a normal module, not under quot;this workbookquot; or a worksheet.
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;CarlosAntennaquot; wrote:
gt; gt;
gt; gt; gt; I also use this same kind of construct often. (vlookup with error
trap)
gt; I
gt; gt; gt; cut and pasted the code into the quot;this workbookquot; object and used the
taz
gt; gt; gt; function in a worksheet but I get a #NAME error. What am I doing
wrong?
gt; I
gt; gt; gt; also tried putting it in the sheet objects -- same error.
gt; gt; gt;
gt; gt; gt; My formula in B2 is: =taz(A2,Sheet1!A:B,2)
gt; gt; gt;
gt; gt; gt; This would be very useful to me but I never thought of defining my own
gt; gt; gt; function.
gt; gt; gt; --
gt; gt; gt; Carlos
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Try this version
gt; gt; gt; gt;
gt; gt; gt; gt; Function taz(a, b, c)
gt; gt; gt; gt;
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt; If IsEmpty(taz) Then
gt; gt; gt; gt; taz = 5
gt; gt; gt; gt; End If
gt; gt; gt; gt; End Function
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;T De Villiersquot;
gt; gt; gt; gt;
gt; gt; gt; gt; wrote in message
gt; gt; gt; gt; news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks for this, another question though, just say I want the
output
gt; to
gt; gt; gt; gt; gt; be 5
gt; gt; gt; gt; gt; when there is an error, why doesnt the following work, many
thanks:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Function taz(a, b, c)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt; gt; If IsError(taz) Then
gt; gt; gt; gt; gt; taz = 5
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; End Function
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; T De Villiers
gt; gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; T De Villiers's Profile:
gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=26479
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=505271
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
Good. Glad you got it working.
--
Kevin Vaughnquot;CarlosAntennaquot; wrote:
gt; Never mind.
gt;
gt; I had more than one book.xlt and I put it in the wrong one.
gt;
gt; --
gt; Carlos
gt;
gt; quot;CarlosAntennaquot; gt; wrote in message
gt; ...
gt; gt; Thanks Kevin, that did it.
gt; gt;
gt; gt; I also tried putting that module in my book.xlt so the function would be
gt; gt; available in new workbooks, but it doesn't appear in the new workbooks
gt; gt; created from book.xlt. Any ideas about that?
gt; gt;
gt; gt; --
gt; gt; Carlos
gt; gt;
gt; gt; quot;Kevin Vaughnquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Put it in a normal module, not under quot;this workbookquot; or a worksheet.
gt; gt; gt; --
gt; gt; gt; Kevin Vaughn
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;CarlosAntennaquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I also use this same kind of construct often. (vlookup with error
gt; trap)
gt; gt; I
gt; gt; gt; gt; cut and pasted the code into the quot;this workbookquot; object and used the
gt; taz
gt; gt; gt; gt; function in a worksheet but I get a #NAME error. What am I doing
gt; wrong?
gt; gt; I
gt; gt; gt; gt; also tried putting it in the sheet objects -- same error.
gt; gt; gt; gt;
gt; gt; gt; gt; My formula in B2 is: =taz(A2,Sheet1!A:B,2)
gt; gt; gt; gt;
gt; gt; gt; gt; This would be very useful to me but I never thought of defining my own
gt; gt; gt; gt; function.
gt; gt; gt; gt; --
gt; gt; gt; gt; Carlos
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bob Phillipsquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Try this version
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Function taz(a, b, c)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt; gt; If IsEmpty(taz) Then
gt; gt; gt; gt; gt; taz = 5
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; End Function
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;T De Villiersquot;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; wrote in message
gt; gt; gt; gt; gt; news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks for this, another question though, just say I want the
gt; output
gt; gt; to
gt; gt; gt; gt; gt; gt; be 5
gt; gt; gt; gt; gt; gt; when there is an error, why doesnt the following work, many
gt; thanks:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Function taz(a, b, c)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; gt; gt; taz = WorksheetFunction.VLookup(a, b, c, 0)
gt; gt; gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt; gt; gt; If IsError(taz) Then
gt; gt; gt; gt; gt; gt; taz = 5
gt; gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; gt; End Function
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; T De Villiers
gt; gt; gt; gt; gt;
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; gt; T De Villiers's Profile:
gt; gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=26479
gt; gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=505271
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;
留言列表
