Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VLookup function in VBA produces #N/A

Posted on 2012-09-10
12
Medium Priority
?
510 Views
Last Modified: 2012-09-12
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?
0
Comment
Question by:hdhondt
  • 4
  • 4
  • 4
12 Comments
 
LVL 36

Expert Comment

by:Norie
ID: 38385523
What values do Allocated and roomcells have in the code?
0
 
LVL 39

Author Comment

by:hdhondt
ID: 38385600
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
 
LVL 50
ID: 38385655
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Expert Comment

by:Norie
ID: 38385661
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
 
LVL 39

Author Comment

by:hdhondt
ID: 38386245
@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
 
LVL 50
ID: 38386279
>> 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
 
LVL 39

Author Comment

by:hdhondt
ID: 38386328
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38386446
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
 
LVL 36

Expert Comment

by:Norie
ID: 38386530
teylyn

Isn't there are 3rd option, use Range(RoomNr).Value in the formula?
0
 
LVL 50
ID: 38386540
Probably. But it might be easier for the asker to get the basic concepts straight before doing that kind of "nesting".
0
 
LVL 36

Expert Comment

by:Norie
ID: 38387680
Have a look at the attachment, I think it does everything you want.
DataTranslationV2.xls
0
 
LVL 39

Author Closing Comment

by:hdhondt
ID: 38390342
Thanks, teylyn. I'll need to take a programming course!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question