PDA

View Full Version : Solved: VLookUp Error in VBA



mbake16
03-15-2009, 01:56 PM
Hi:

I've cannot get vLookUp to work properly within my User Defined Function. In the simplified example attached, you'll see the vLookUp UDF is not correctly finding the List1 items in List2...although they are the exact same items. If anyone can point out the error in my code or shed some light on possible issues in using vLookUp in macros or UDF's that would be great. :help

The UDF code in the attached is also below for reference:


Function UPCLookup(UPC As Range)
Dim lookuplist As Range
Set lookuplist = Sheet2.Range("a2:a26")
UPCLookup = Application.WorksheetFunction.VLookup(UPC.Value, lookuplist, 1)
End Function


Thanks,

Matt

mdmackillop
03-15-2009, 03:19 PM
You appear to have a misunderstanding of vlookup, which is intended to return a value offset from the found value (see attachment). What is it you are trying to do?

mbake16
03-16-2009, 05:18 AM
Thanks, I appreciate your response. I've attached the actual file I'm working with to give you a clearer understanding of what I'm trying to do. In the worksheet, "POG Ranking", I'm using a User Defined Function, "Weeks" (located in Module1), in the yellow highlighted cells in column N to vlookup this worksheet's column C values within worksheet, "Step 2". Once the item is found it is to return the worksheet, "Step 2", column BD value to worksheet, "POG Ranking", column N. I initiallly used the vlookup function directly in Excel and verified that it works correctly so I can't figure out why my User Defined Function, which also uses the vLookUp formula will not properly work. Any insight would be great, thank you!

Matt

Bob Phillips
03-16-2009, 06:24 AM
I can see no use of UPCLookup, and the project is password protected, so you are not helping yourself.

mbake16
03-16-2009, 09:15 AM
Very sorry about not including the VBA password, it is: charliebravo

Thank you again,

Matt

Bob Phillips
03-16-2009, 09:31 AM
Your Weeks function is missing an End If. There may be other problems, but I can't work it as there is a reference to ACNielsen NITRO range Object which I cannot remove.

mbake16
03-16-2009, 12:37 PM
Thank you again for taking a look at this. I've stripped out the ACNielsen range to eliminate the reference to that object. Also, I added and "End If" to the If statement but a reference error is still returned. The updated file is reattached.

Sorry for making this so difficult for you to provide help on this.


Matt

Bob Phillips
03-16-2009, 01:50 PM
You have a defined name called Weeks as well, so the cell refers to the name, not the function.

mbake16
03-16-2009, 05:56 PM
Thank you! I've removed the Define name, "Weeks". The error changes to #value. Attached is the latest file. Any thoughts?

Sagy
03-17-2009, 11:03 AM
Set WeeksRange = Sheet5.Range("b4:bd40000")
...
Weeks = Application.WorksheetFunction.VLookup(UPC.Value, WeeksRange, 56)
The 56 should be replaced with 55. "bd" is column 56, bust since your range starts in "b" (column 2), it is the 55th column in the range.

You might also want to consider using the 4th parameter to the Vlookup(,,,FALSE) so it will only return a value if an exact match was found (if you don't want exact match, use TRUE as the 4th parameter).

You might aslo want to consider replacing your Weeks VBA macro with

=IF(C11="","",VLOOKUP(C11,'Step 2'!B:BD,55,FALSE))

mbake16
03-18-2009, 06:36 AM
Awesome! Thank you so much for taking the time to point out my incorrect column index. Can't believe I did that! Also, thanks for the suggestion on simply using an IF statement directly in Excel, it works great. I also look up the difference between using "True" and "False" as the 4th parameter, based on your comments. I didn't realize the difference between the two. You've been extremely helpful!