Doing a macro in Excel 2010. It opens a workbook, then adds a worksheet from another book that contains several lookup tables set up as named ranges each with workbook scope.

When I get to the For loop and try to invoke Worksheet.Function.VLookup to pull values from those named ranges, it gives me the above error. Posts I have read suggest the value is not being found. That's not it, the values are there, and I can type the VLookup manually and it resolves the lookup no problem.

It's more basic than that. The function doesn't seem to be recognizing the named ranges. Is my syntax for addressing those correct? I've tried Range("Arrays!questionlookup") without success.

Dim file2open As StringDim anotherfile2open As StringDim mainbook As WorkbookDim rangebook As WorkbookDim file_name As StringDim rowz As IntegerDim colz As IntegerDim lastrow as integerDim question As IntegerDim audience As IntegerDim recog As IntegerDim metric As IntegerDim measure As Integerfile_name = "sourcedata.csv"file2open = sourcefolder & "\" & file_nameSet mainbook = Workbooks.Open(Filename:=file2open)Set datasheet = mainbook.Worksheets(1)anotherfile2open = sourcefolder & "\" & "lookupranges.xlsx"Set rangebook = Workbooks.Open(Filename:=anotherfile2open)rangebook.Worksheets("Arrays").Copy After:=mainbook.Sheets(mainbook.Sheets.Count)rangebook.Closedatasheet.Activatelastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).RowFor rowz = 2 To lastrow' next line is where I get the error question = WorksheetFunction.VLookup(Cells(rowz, 1), questionlookup, 2, False) audience = WorksheetFunction.VLookup(Cells(rowz, 2), audiencelookup, 2, False) recog = WorksheetFunction.VLookup(Cells(rowz, 3), recoglookup, 2, False) metric = WorksheetFunction.VLookup(Cells(rowz, 4), metriclookup, 2, False) Data(question, audience, recog, metric, 1) = Cells(rowz, 5) Data(question, audience, recog, metric, 2) = Cells(rowz, 6) Data(question, audience, recog, metric, 3) = Cells(rowz, 7) Data(question, audience, recog, metric, 4) = Cells(rowz, 8)Next rowz

vlookup looks into a range. Right now you're looking at one cell and seeing if it matches, i.e. your vba formula for the first row boils down to the following excel formula, with just ONE cell. In addition, you haven't defined questionlookup in your code, so you're looking to find Nothing in one cell.

=VLookup(A2, questionlookup, 2, False)

Is that your full code? What is the working manual vlookup formula that gives you the correct answer?

The formula you put in your response is precisely the same as the manual Vlookup formula I typed, and when entered manually like this it does give me the correct answer.

questionlookup, audiencelookup, recoglookup, and metriclookup are not defined in the macro because they are named ranges, found on the "Arrays" worksheet that I copied into the workbook near the beginning of the macro. They have workbook scope, so should be addressable by name.

That's the heart of my question. Is there something about referring to a named range from within the code that doesn't work the same as referring to a named range from a manually typed formula in a cell? Does the fact that I just brought the "Arrays" sheet into the workbook have anything to do with the fact that the code doesn't recognize the named ranges?

Here's a screenshot of the named ranges on the Arrays sheet. As you can see they are very simple, and the first column is alpha sorted in each case.

You are referencing to a not defined VBA variable with the name questionlookup, and not the named range on the worksheet.
Similar to the VBA variable rowz.

Change to [questionlookup] or Range("questionlookup") like this

I wasn't aware that you need to bracket named ranges when referring to them in VBA code.

To answer your question, nutsch, each row of my sheet (see image below) has four columns which contain string values that are tied to the numbers in the last 4 columns. So for each row, I first find the equivalent integer for that key. Say, for example, my lookups resolve to 3, 1, 2, 1. Then I store the number in the next column into the array Data (3,1,2,1,1) , then Data (3,1,2,1,2), etc. Later in my routines I'll want to be able to pull up the "lo" value for "Q37_4, ITDM, no, current" and I know it will be stored in Data (3,1,2,1,3)

Can you think of a faster way to do this?

0

Featured Post

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!

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.

Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…