Hi , the following vlookup to change vlookup ranges when there is an
error
doesnt work, many thanks for any help


Function taz(a)
On Error Resume Next
taz = WorksheetFunction.VLookup(a, Range("a1:b3"), 2, 0)


If IsEmpty(taz) Then
taz = Worksheet.Function.VLookup(a, Range("a10:b12"), 2, 0)

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=505673

I'd drop the worksheetfunction prefix and use application.vlookup().

Function taz(a) as variant
dim res as variant
res = application.vlookup(a, Range("a1:b3"), 2, 0)

if iserror(res) then
res = application.vlookup(a, Range("a10:b12"), 2, 0)
end if

if iserror(res) then
taz = "Not found"
else
taz = res
end if

End Function

But I'd be careful with those ranges. I'd specify them in the worksheet
function:

=taz(a1,sheet2!a1:b3,sheet99!a10:b12)

so that excel will know how to calculate when a value in any of those 3 ranges
changes.

Function taz(a as range, lookuprng1 as range, lookuprng2 as range) as variant
dim res as variant
res = application.vlookup(a, lookuprng1, 2, 0)

if iserror(res) then
res = application.vlookup(a, lookuprng2, 2, 0)
end if

if iserror(res) then
taz = "Not found"
else
taz = res
end if

End Function

If you don't want to do that, then I think the least you should do is specify
the worksheets for those ranges and make the UDF volatile:

Function taz(a as range, lookuprng1 as range, lookuprng2 as range) as variant
application.volatile
dim res as variant
res = application.vlookup(a, worksheets("sheet1").range("a1:b3"), 2, 0)

if iserror(res) then
res = application.vlookup(a, worksheets("sheet99").Range("a10:b12"), 2, 0)
end if

if iserror(res) then
taz = "Not found"
else
taz = res
end if

End Function

======
And if you're not using this in a worksheet cell, then ignore the volatile
stuff. But if you are, remember that the results could be one calculation
behind--don't trust them without recalculating first.

T De Villiers wrote:

Hi , the following vlookup to change vlookup ranges when there is an
error
doesnt work, many thanks for any help

Function taz(a)
On Error Resume Next
taz = WorksheetFunction.VLookup(a, Range("a1:b3"), 2, 0)

If IsEmpty(taz) Then
taz = Worksheet.Function.VLookup(a, Range("a10:b12"), 2, 0)

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=505673

--

Dave Peterson