Shortening a vlookup

[T De Villiers]

I use the following type of function a lot:
if(iserror(vlookup(a,sheet2!$a$1:$d$15,2,0)),0,vlookup(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

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[T De Villiers]

Thanks for this, another question though, just say I want the output t
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 Functio

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[T De Villiers]

Thanks for this Bob - you're extremely helpful

 

[CarlosAntenna]

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

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

 

[Guest]

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

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.

 

[CarlosAntenna]

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?

 

[CarlosAntenna]

Never mind.

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

 

[Guest]

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

Never mind.

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