MrExcel.com - Your One Stop for Excel Tips & Solutions

Using VBA to process Vlookups in multiple workbooks

Posted by Corey on November 15, 2001 1:29 PM

I am currently writing a macro suing VBA to process vlookups using two workbooks. I am unable to get the vlookup function to recognize the named range in another workbook. Here is what my code looks like.
Workbooks.Open FileName:=Ans1 'New workbook
Ans = ActiveSheet.Name 'Workbook sheet
Names.Add Name:="Fund_List", RefersToR1C1:="=" + (Ans) + "!R9C2:R500C10" 'Range named
File = "MA.xls" 'Main workbook
F = Workbooks(File).Worksheets(Ans).Range("C1001").Offset(Ctr, 0).Value 'File Name
R1 = Workbooks(Ans1).Worksheets(Ans).Range("Fund_List") 'Row Reference
C = 5 'Column Number
V = Application.WorksheetFunction.VLookup(F, R1, C, False)

Any suggestions?

Corey

Posted by Omar Sivori on November 15, 2001 4:22 PM

I'm having trouble getting the printer to cooperate with me. Imagine that!
I have a couple of pie charts where each section is a different pattern - black and white lines only (vertical, horizontal, diagonal and criss-crossed)...for some reason when it prints, regardless of what I set my printer to, it comes out in grey scale. Is there anything I can do?

Posted by Omar Sivori on November 15, 2001 4:24 PM


Try changing the last line to :-

v = Application.VLookup(F, R1, C, False)

Vlookup sometimes doesn't work with the syntax "Application.WorksheetFunction.VLookup".

Posted by Juan Pablo on November 16, 2001 6:09 AM

Make sure that V is defined as Variant. The problem is that, if Vlookup can't find a value (#N/A in a function), the Worksheetfunction.Vlookup WILL crash, but Application.Vlookup won't....strange, right ? try to use Application. instead of Worksheetfunction.

Juan Pablo