Need syntax for lookup in another open workbook

**keris** · 12-11-2009, 04:21 PM

I'm writing a macro which will be used for a monthly report. It will always be run with three workbooks open, but the names of the workbooks change from month to month.

Once the workbooks are open, the macro captures each window's title (ActiveWindow.Caption) and uses it to set the value of a variable. Thus these are the three variables that refer to my open workbooks:
- Data1
- Data2
- WorkDoc

For testing purposes, I've saved this month's WorkDoc workbook with the name "RedWings." No matter its name, this workbook always consists of only one worksheet. In this case that worksheet's name is "Osgood."

I would like to enter a lookup function into cell J2 of Data1. I want it to take the contents of Data1's cell F2, look it up in column A of WorkDoc, find the corresponding value in column B of WorkDoc, and place that value into J2 of Data1.

If I enter this formula directly into cell J2 of Data1 (not using a macro), it works fine:
=LOOKUP(F2,[redwings.xls]osgood!$A:$A,[redwings.xls]osgood!$B:$B)

What syntax would I use for coding that formula into the macro?

Here's what I've got:
Range("J2").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-4],WorkDoc.Sheets(1)!$A:$A,WorkDoc.Sheets(1)!$B:$B)"

The macro works fine until that point. Then I get:
Run-time error '1004':
Application-defined or object-defined error

Any idea what may be causing this and how to fix it? RedWings does contain the necessary data in the necessary ranges. Thanks in advance for your help.

**davegugg** · 12-11-2009, 04:37 PM

My guess is it may be because you are refering to the vba's Sheets(1). I'm not sure that you can refer to sheets by their index in excel's cells. My guess is you'll need a variable to capture the sheets name (if it isn't always going to be the same). I'd also do the same for the value you are searching for.

You have:

Code:

ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-4],WorkDoc.Sheets(1)!$A:$A,WorkDoc.Sheets(1)!$B:$B)"

Try:

Code:

Dim wsName As String
Dim SearchValue As String

wsName = ActiveWorkbook.Sheets(1).Name
SearchValue = ActiveCell.Offset(0, -4).Value

'If you are searching for text in Lookup, use:
ActiveCell.Formula = "=LOOKUP(""" & SearchValue & """," & wsName & "!$A:$A," & wsName & "!$B:$B)"

'If you are searching for a numeric value, use:
ActiveCell.Formula = "=LOOKUP(" & SearchValue & "," & wsName & "!$A:$A," & wsName & "!$B:$B)"

**keris** · 12-18-2009, 01:19 AM

Thanks for your answer! Due to a family illness and a crazy schedule at work, I haven't had time to try it out yet, but I plan to in the next few days. I'll let you know how it goes.

**keris** · 12-20-2009, 08:30 PM

That inserted the proper formula, but immediately afterward (before finalizing the contents of cell J2), Excel puts up a window called "Update values:Osgood" and wants me to locate RedWings. Will it do this every month? If so, can I automate the process?

Edited to note that it doesn't insert the proper formula. Even after I locate RedWings and select it, and the "Update values: Osgood" window goes away, the formula it inserts is as follows:
=LOOKUP("texttolookup",Osgood!$A:$A,Osgood!$B:$B)
and the value of this formula turns out to be blank.

Thanks,
Keri

**royUK** · 12-21-2009, 04:06 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here