VLookup function in VBA produces #N/A

I have an Excel spreadsheet macro that converts a room number in a spreadsheet to a room name, by using VLookup. The macro runs fine but the results in every target cell is #N/A.

The line that does the work is:
Range(Allocated) = Application.VLookup(RoomNr, Range(roomcells), 2)

RoomNr is the cell with the room number, e.g. "E2"
Roomcells is the table of room names and numbers. Its value is "[DataTranslation.xls]Sheet1!A1:B22"
Allocated is the cell where the room name will go, e.g. "H2"

If I use this as a spreadsheet function, i.e. when the function is H2 is
=VLOOKUP(E2,[DataTranslation.xls]Sheet1!A1:B22,2)
then it works fine.

What am I doing wrong?
LVL 39
hdhondtAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

There are a few things wrong with that code.

I think there may be a bit of confusion as to how the WorksheetFunction and variables work in VBA. If you use Application.WorksheetFunction  like Vlookup in VBA, you need to pass it  cell ranges, not text values that look like cell ranges.

For example, this line of code ...

RoomNr = "e2" 

Open in new window


... will assign the TEXT VALUE "e2" to the variable RoomNr. Hence, when you plug the RoomNr into the Vlookup, it will perform like

Vlookup("e2", etc ...)

But what you really want is

Vlookup(E2, etc ...)

You have two possibilities. Either assign the VALUE of E2 to the RoomNr variable, like

RoomNr = Range("E2").value

Open in new window


or declare the RoomNr as a range variable and  pass the range to the variable

Dim RoomNr as Range
Set RoomNr = Range("E2")

Open in new window


The same goes for the lookup range and how it is declared. You are just building a text string, but not a range of cells.

In general, you should declare variables before using them. Try preceding your code with Option Explicit, so you cannot make up variables on the fly. This will help to make things a bit more structured.

cheers, teylyn
0
 
NorieVBA ExpertCommented:
What values do Allocated and roomcells have in the code?
0
 
hdhondtAuthor Commented:
If I run the macro in debug mode, the values are as I gave them, i.e. allocated is a cell, like "H10" and roomcells has the value "[DataTranslation.xls]Sheet1!A1:B22"
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

can you post the complete code? How is the variable RoomNr defined and how is it populated? N/A means that a value cannot be found. This is a strange result for VLookup when you omit the 4th parameter. The most likely reason is that the data types are not the same for RoomNr and the first column of the lookup range.

Check out this article from matthewspatrick to troubleshoot your Vlookup http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

Another thought: the definition of the room range you post above uses relative cell references. Is that really the case? If so, the range will change based on the position of the current cell.

cheers, teylyn
0
 
NorieVBA ExpertCommented:
What happens if you replace the code with this?
Range(Allocated) = Application.VLookup(RoomNr, Range(roomcells), 2, 0)

Open in new window


Or this:
Range(Allocated) = Application.VLookup(RoomNr,
Workbooks("DataTranslation").Worksheets("Sheet1").Range("A1:B22"), 2, 0) 

Open in new window

Also is the DataTranslation.xls workbook open?
0
 
hdhondtAuthor Commented:
@teylyn

The funny thing is that, when I run it in Debug, all the variables seem to have the correct values. RoomNr and column 1 of the lookup range have general format. I have tried changing the to text and number, but that made no difference. I'll try absolute formatting.

@imnorie

I've tried those suggestions, but the result is the same. And yes, DataTranslation is open - the macro is in that workbook.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>> RoomNr and column 1 of the lookup range have general format

That does not necessarily mean anything. The lookup value could be a number stored as text, whereas the lookup table has numbers in the first column (or vice versa). Or the lookup value could have leading or trailing blanks, etc. The "General" format applied to the cell(s) does not make a difference in these cases.

Why don't you post a sample file that presents the problem, so we can see the whole picture? That will make it a lot easier to troubleshoot.

cheers, teylyn
0
 
hdhondtAuthor Commented:
That's why I tried changing the format from general to number and text.

I have attached 2 files. Data Translation has the lookup table and the macro, the other file is the one the macro runs on. The macro is a mess at present as I've been choppinmg and changing it - and because I'm an amateur, not a pogrammer
20120903.csv
DataTranslation.xls
0
 
NorieVBA ExpertCommented:
teylyn

Isn't there are 3rd option, use Range(RoomNr).Value in the formula?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Probably. But it might be easier for the asker to get the basic concepts straight before doing that kind of "nesting".
0
 
NorieVBA ExpertCommented:
Have a look at the attachment, I think it does everything you want.
DataTranslationV2.xls
0
 
hdhondtAuthor Commented:
Thanks, teylyn. I'll need to take a programming course!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.