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: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=505271

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 = 0
End If
End Function



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"T De Villiers"
wrote in message
news:T.De.Villiers.228qdz_1138276809.0729@excelfor um-nospam.com...

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:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=505271

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: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=505271

Try 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)

"T De Villiers"
wrote in message
news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...

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:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=505271

Thanks for this Bob - you're extremely helpful


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=505271

I also use this same kind of construct often. (vlookup with error trap) I
cut and pasted the code into the "this workbook" 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

"Bob Phillips" wrote in message
...
Try 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)

"T De Villiers"

wrote in message
news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...

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:
http://www.excelforum.com/member.php...o&userid=26479
View this thread:
http://www.excelforum.com/showthread...hreadid=505271

Put it in a normal module, not under "this workbook" or a worksheet.
--
Kevin Vaughn


"CarlosAntenna" wrote:

I also use this same kind of construct often. (vlookup with error trap) I
cut and pasted the code into the "this workbook" 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

"Bob Phillips" wrote in message
...
Try 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)

"T De Villiers"

wrote in message
news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...

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:
http://www.excelforum.com/member.php...o&userid=26479
View this thread:
http://www.excelforum.com/showthread...hreadid=505271

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

"Kevin Vaughn" wrote in message
...
Put it in a normal module, not under "this workbook" or a worksheet.
--
Kevin Vaughn


"CarlosAntenna" wrote:

I also use this same kind of construct often. (vlookup with error trap)
I
cut and pasted the code into the "this workbook" 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

"Bob Phillips" wrote in message
...
Try 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)

"T De Villiers"

wrote in message
news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...

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:
http://www.excelforum.com/member.php...o&userid=26479
View this thread:
http://www.excelforum.com/showthread...hreadid=505271

Never mind.

I had more than one book.xlt and I put it in the wrong one.

--
Carlos

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

"Kevin Vaughn" wrote in message
...
Put it in a normal module, not under "this workbook" or a worksheet.
--
Kevin Vaughn


"CarlosAntenna" wrote:

I also use this same kind of construct often. (vlookup with error
trap)
I
cut and pasted the code into the "this workbook" 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

"Bob Phillips" wrote in message
...
Try 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)

"T De Villiers"

wrote in message
news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...

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:
http://www.excelforum.com/member.php...o&userid=26479
View this thread:
http://www.excelforum.com/showthread...hreadid=505271

Good. Glad you got it working.
--
Kevin Vaughn


"CarlosAntenna" wrote:

Never mind.

I had more than one book.xlt and I put it in the wrong one.

--
Carlos

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

"Kevin Vaughn" wrote in message
...
Put it in a normal module, not under "this workbook" or a worksheet.
--
Kevin Vaughn


"CarlosAntenna" wrote:

I also use this same kind of construct often. (vlookup with error
trap)
I
cut and pasted the code into the "this workbook" 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

"Bob Phillips" wrote in message
...
Try 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)

"T De Villiers"

wrote in message
news:T.De.Villiers.228spa_1138279803.9027@excelfor um-nospam.com...

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:
http://www.excelforum.com/member.php...o&userid=26479
View this thread:
http://www.excelforum.com/showthread...hreadid=505271