Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run-time Error 1004:  Unable to get the VLookup property of the WorksheetFunction class

Posted on 2014-01-21
5
Medium Priority
?
5,501 Views
Last Modified: 2014-01-27
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 String
Dim anotherfile2open As String
Dim mainbook As Workbook
Dim rangebook As Workbook
Dim file_name As String
Dim rowz As Integer
Dim colz As Integer
Dim lastrow as integer
Dim question As Integer
Dim audience As Integer
Dim recog As Integer
Dim metric As Integer
Dim measure As Integer

file_name = "sourcedata.csv"
file2open = sourcefolder & "\" & file_name
Set 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.Close

datasheet.Activate

lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For 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

Open in new window

sourcefolder and the Data array are public variables, by the way.

Any ideas?  Thanks.
0
Comment
Question by:Bryce Bassett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39798755
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?

Thomas
0
 

Author Comment

by:Bryce Bassett
ID: 39800489
Thanks, Thomas.

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.

arrays worksheet
Thanks
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 1600 total points
ID: 39800650
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

question = WorksheetFunction.VLookup(Cells(rowz, 1), [questionlookup], 2, False)
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39800789
In addition to hgholt's very valid point, I still wonder why you'd run a loop to do a vlookup cell by cell?
0
 

Author Comment

by:Bryce Bassett
ID: 39801800
Exactly what I needed!  Thanks.

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?

data sheet
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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