software

Hi , the following vlookup to change vlookup ranges when there is an
error
doesnt work, many thanks for any helpFunction taz(a)
On Error Resume Next
taz = WorksheetFunction.VLookup(a, Range(quot;a1:b3quot;), 2, 0)If IsEmpty(taz) Then
taz = Worksheet.Function.VLookup(a, Range(quot;a10:b12quot;), 2, 0)

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=505673I'd drop the worksheetfunction prefix and use application.vlookup().

Function taz(a) as variant
dim res as variant
res = application.vlookup(a, Range(quot;a1:b3quot;), 2, 0)

if iserror(res) then
res = application.vlookup(a, Range(quot;a10:b12quot;), 2, 0)
end if

if iserror(res) then
taz = quot;Not foundquot;
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 = quot;Not foundquot;
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(quot;sheet1quot;).range(quot;a1:b3quot;), 2, 0)

if iserror(res) then
res = application.vlookup(a, worksheets(quot;sheet99quot;).Range(quot;a10:b12quot;), 2, 0)
end if

if iserror(res) then
taz = quot;Not foundquot;
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:
gt;
gt; Hi , the following vlookup to change vlookup ranges when there is an
gt; error
gt; doesnt work, many thanks for any help
gt;
gt; Function taz(a)
gt; On Error Resume Next
gt; taz = WorksheetFunction.VLookup(a, Range(quot;a1:b3quot;), 2, 0)
gt;
gt; If IsEmpty(taz) Then
gt; taz = Worksheet.Function.VLookup(a, Range(quot;a10:b12quot;), 2, 0)
gt;
gt; End If
gt;
gt; End Function
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=505673

--

Dave Peterson