VLookup in VBA returning an empty string Why I was expecting the value I was serching

[keithaul]

I have a piece of code where a user types in a value and when the user clicks a command button on a userform the code does a VLookup on a named list with 1 column in that list. However when i debug that code VLookup returns an empty string even when VLookup finds the value used in the arguments. Below is the code I'm using:

txtVLUpValue = Application.WorksheetFunction.VLookup(txtValue, AccessCodeList, 1, False)

When I use this VLookup in my spreadsheet in the formula bar the image below shows that it returns the value into the cell:

16702

Why do you think the VLookup in VBA returns a "" (empty string) even though it found the value in the list?

The purpose of my code is for a user to type a value into the userform and then check that value in my named list of 1 column. If it finds the value, then success.

Am I misinterpreting what VLookup should return in VBA?

[mdmackillop]

Use Match instead.

[keithaul]

Im trying the following code and it is not working the way I expect it to:

doesnotexist = WorksheetFunction.Match(txtValue, AccessCodeList, 0)

The 3rd value in the match function I set to 0 to get the exact match. I thought if its an exact match, it returns the row # of where the values exists in the list. If it doen't exist it returns #N/A.

Everytime I run the above code and the value doesn't exist, my variable remains empty. The same is true if the value does exist

How do I use this function in VBA to check if the value exists or doesn't exist? I've attempted to surround Match with the IFNA function and I get similar results.

Keith

[mdmackillop]

doesnotexist = IsError(Application.Match(txtvalue, AccessCodeList, 0))

[keithaul]

No matter what value I put in, this IsError always returns True. I've tested it with multiple values in my list and the IsError returns true. Even values that don't exist in the list, this function returns true. My variable is declared as a boolean.

Also, what is the difference with WorksheetFunction.Match and Application.Match?

[p45cal]

When I use this VLookup in my spreadsheet in the formula bar the image below shows that it returns the value into the cellThat picture is NOT showing that it's found the value and is returning it, that is simply what you're looking FOR. (Did you do an F9 on the cell address in the formula to get that?)
It's far more likely that there is a small difference between what you're looking for and where you think it is in the list (especially as you're using FALSE as the last argument of the VLOOKUP); perhaps a trailing or leading space, something else. This is especially more likely since the strings you're searching through seem to contain less frequently used characters (are there any linefeeds in AccessCodeList?).
Why don't you put together a workbook with a single sheet showing this failure, with a short bit of vba also showing failure to find stuff and I can guarantee you I/someone can tell you why. A picture doesn't show the whole picture (oxymoronic/paradoxical or what?).

Oh, one more thing, and this could be the problem, I see you've used AccessCodeList both in the formula and in the vba code. You do realise that you're not referring to the Name AccessCodeList in the vba code? They are not one and the same. You might have to assign that range to the variable in vba with a prior line such as:
Set AccessCodeList=Range("AccessCodeList")
or change your existing line of code to:
txtVLUpValue = Application.WorksheetFunction.VLookup(txtValue, range("AccessCodeList"), 1, False)

[keithaul]

The picture shows the result of the formula in cell B4. So it did find the value of the first argument in VLookup

i did not know about doing F9. What will this do?

The values in this list were created using various forms of the Random function. As far as I know there are no trailing or leading spaces

Each cell of the list there are no blanks and I don't think there are line feeds. Would the random functions generate this?

AccessCodeList appears in my name manager. I didn't realize that VBA code treats names in the name manager different. So why are they treated different?

I will have to try your suggestion and get back here with the results.

Also, what is the reason for including Application.WorsheetFunction. in front of the function name in VBA code? Why can't we just using the function name alone with out that?

Thanks,
Keith

[Aussiebear]

Have a look here regarding the F9 key. https://exceljet.net/tips/how-to-check-and-debug-a-formula-with-f9

[p45cal]

The picture shows the result of the formula in cell B4. So it did find the value of the first argument in VLookupTrue enough!


i did not know about doing F9. What will this do?See msg#8


The values in this list were created using various forms of the Random function. As far as I know there are no trailing or leading spaces
Each cell of the list there are no blanks and I don't think there are line feeds. Would the random functions generate this?That depends entirely on how the random functions are crafted.


AccessCodeList appears in my name manager. I didn't realize that VBA code treats names in the name manager differently. So why are they treated differently?They are very different animals. Names don't have to be ranges, for example. They can be constants, formulae and probably a number of other things.


I will have to try your suggestion and get back here with the results.So it looks as if this might be the root of the problem.


Also, what is the reason for including Application.WorsheetFunction. in front of the function name in VBA code? Why can't we just using the function name alone with out that?There are a bunch of functions native to vba, and it looks like someone thought that being able to use worksheet functions too in vba might be a good thing. You can miss out the WorksheetFunction bit or the Application bit, but not miss out both. The only difference between them I know of is in what vba does when the function generates an error.