VLookup function in VBA produces #N/A
Posted on 2012-09-10
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
then it works fine.
What am I doing wrong?