Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup
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").Cel ls(e,f)), b, 0) If IsError(res) Then res = Application.VLookup(a, Range(Sheets("Jobs").Cells(c,d),Sheets("Jobs").Cel ls(e,f)), b, 0) End If If IsError(res) Then taz = 0 Else taz = res 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=567917 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup
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 -- Regards, Tom Ogilvy "T De Villiers" wrote: 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").Cel ls(e,f)), b, 0) If IsError(res) Then res = Application.VLookup(a, Range(Sheets("Jobs").Cells(c,d),Sheets("Jobs").Cel ls(e,f)), b, 0) End If If IsError(res) Then taz = 0 Else taz = res 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=567917 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup
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. T De Villiers wrote: 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").Cel ls(e,f)), b, 0) If IsError(res) Then res = Application.VLookup(a, Range(Sheets("Jobs").Cells(c,d),Sheets("Jobs").Cel ls(e,f)), b, 0) End If If IsError(res) Then taz = 0 Else taz = res 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=567917 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 wrote: 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").Cel ls(e,f)), b, 0) If IsError(res) Then res = Application.VLookup(a, Range(Sheets("Jobs").Cells(c,d),Sheets("Jobs").Cel ls(e,f)), b, 0) End If If IsError(res) Then taz = 0 Else taz = res 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=567917 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup
Many Thanks Tom -- 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=567917 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |