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!questionlook
up") 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)
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)
sourcefolder and the Data array are public variables, by the way.
Any ideas? Thanks.