vlookup

[T De Villiers]

this is my original function, works fine,
however the lookup range can vary greatly, 2nd version is below,
not quite working, help on this one is much appreciated

Function taz(a, b) As Variant
Dim res As Variant
res = Application.VLookup(a, Range("Jobs!$A$1:$F$65000"), b, 0)

If IsError(res) Then
res = Application.VLookup(a, Range("Jobs!$A$1:$F$65000"), b, 0)
End If

If IsError(res) Then
taz = 0
Else
taz = res
End If

End Function

Version 2:

Function taz(a, b,c,d,e,f) As Variant
Dim res As Variant
res = Application.VLookup(a,
Range(Sheets("Jobs").Cells(c,d),Sheets("Jobs").Cells(e,f)), b, 0)

If IsError(res) Then
res = Application.VLookup(a,
Range(Sheets("Jobs").Cells(c,d),Sheets("Jobs").Cells(e,f)), b, 0)

End If

If IsError(res) Then
taz = 0
Else
taz = res
End If

End Function

 

[Guest]

Function taz(a, b,c,d,e,f) As Variant
Dim res As Variant
With Worksheets("Jobs")
res = Application.VLookup(a, _
.Range(.Cells(c,d),.Cells(e,f)), b, 0)

End With

If IsError(res) Then
taz = 0
Else
taz = res
End If
End With
End Function

You original function may work, but it doesn't need the second identical
lookup:

Function taz(a, b) As Variant
Dim res As Variant
res = Application.VLookup(a, Range("Jobs!$A$1:$F$65000"), b, 0)

If IsError(res) Then
taz = 0
Else
taz = res
End If

End Function

 

[Dave Peterson]

What does not quite working mean?

If it means that it only gives the correct answers if you recalculate the
formula--not when any of the data changes, that's because excel doesn't know
what the function depends on -- so it doesn't know when to recalculate.

If that's not it, maybe it's the parms you're passing to the function.

And I don't know what you're doing in either function, but you seem to be doing
the same thing twice.

Untested....

Function taz(a, b,c,d,e,f) As Variant
Dim res As Variant
dim myRng as range

set myrng = nothing
on error resume next
with worksheets("Jobs")
set myrng = .range(.cells(c,d),.cells(e,f))
on error goto 0

if myrng is nothing then
taz = "Error in c,d,e,f!"
exit function
end if

'just for testing
msgbox myrng.address(0,0)

If IsError(res) Then
taz = "error in vlookup" 'used to b 0
Else
taz = res
End If

End Function

But I'm not sure what you're passing--I think I'd add some more validity
checks. (Really, I'd just use the =vlookup() worksheet function.

this is my original function, works fine,
however the lookup range can vary greatly, 2nd version is below,
not quite working, help on this one is much appreciated

Function taz(a, b) As Variant
Dim res As Variant
res = Application.VLookup(a, Range("Jobs!$A$1:$F$65000"), b, 0)

If IsError(res) Then
res = Application.VLookup(a, Range("Jobs!$A$1:$F$65000"), b, 0)
End If

If IsError(res) Then
taz = 0
Else
taz = res
End If

End Function

Version 2:

Function taz(a, b,c,d,e,f) As Variant
Dim res As Variant
res = Application.VLookup(a,
Range(Sheets("Jobs").Cells(c,d),Sheets("Jobs").Cells(e,f)), b, 0)

If IsError(res) Then
res = Application.VLookup(a,
Range(Sheets("Jobs").Cells(c,d),Sheets("Jobs").Cells(e,f)), b, 0)

End If

If IsError(res) Then
taz = 0
Else
taz = res
End If

End Function

res = Application.VLookup(a, myrng,b,0)

If IsError(res) Then
taz = "Error in Vlookup" 'used to be 0
Else
taz = res
End If

End Function

I think I'd add some additional checks, too.

 

[T De Villiers]

Many Thanks Tom