Use a lookup with a variable for the workbook name

  • Thread starter Thread starter Wilbur
  • Start date Start date
W

Wilbur

I have a workbook and I have to use INDEX(...,MATCH(...)) into another
workbook that I have opened and it does not accept the code:
workbook is "2006 MasterMaterials.xls" with worksheet "RawMaterial"

cString = "2006 MasterMaterials.xls"

FormulaR1C1 = "=INDEX([cString]RawMaterial!$J:$J,
MATCH(C3,[cString]RawMaterial!$C:$C,0))"

Anyone have ideas???? I have tried a number of things.

Thanks
 
This won't work, but it gets you closer:

..FormulaR1C1 = "=INDEX([" & cString & "]RawMaterial!$J:$J," & _
"MATCH(C3,[" & cString & "]RawMaterial!$C:$C,0))"

But you've got another problem. .FormulaR1C1 wants the formula in R1C1
reference style.

Or maybe you want:

..Formula = "=INDEX([" & cString & "]RawMaterial!$J:$J," & _
"MATCH(C3,[" & cString & "]RawMaterial!$C:$C,0))"

I have a workbook and I have to use INDEX(...,MATCH(...)) into another
workbook that I have opened and it does not accept the code:
workbook is "2006 MasterMaterials.xls" with worksheet "RawMaterial"

cString = "2006 MasterMaterials.xls"

FormulaR1C1 = "=INDEX([cString]RawMaterial!$J:$J,
MATCH(C3,[cString]RawMaterial!$C:$C,0))"

Anyone have ideas???? I have tried a number of things.

Thanks
Click to expand...
 
Wilbur,

Take the variable cString out of the quotes. Try:

FormulaR1C1 = "=INDEX([" & cString & "]RawMaterial!$J:$J,MATCH(C3,[" &
cString & "]RawMaterial!$C:$C,0))"

HTH

Tim
 
Oh! and it should start with something like:

Range("A4").Formula =

and not 'FormulaR1C1' when you use absolute cell references.

Tim

Tim Barlow said:
Wilbur,

Take the variable cString out of the quotes. Try:

FormulaR1C1 = "=INDEX([" & cString & "]RawMaterial!$J:$J,MATCH(C3,[" &
cString & "]RawMaterial!$C:$C,0))"

HTH

Tim

Wilbur said:
I have a workbook and I have to use INDEX(...,MATCH(...)) into another
workbook that I have opened and it does not accept the code:
workbook is "2006 MasterMaterials.xls" with worksheet "RawMaterial"

cString = "2006 MasterMaterials.xls"

FormulaR1C1 = "=INDEX([cString]RawMaterial!$J:$J,
MATCH(C3,[cString]RawMaterial!$C:$C,0))"

Anyone have ideas???? I have tried a number of things.

Thanks
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using one combo box to narrow the selection in another combo box 6
ATL::CString and operator<< 2
Reference a workbook level named variable. 8
Save As and new workbook new name used in existing code 8
referencing a workbook using string var = error9 1
Array formula based on Chip Pearson's lookup data samples 1
Getting coordinates of an Hyperlink using range get_Top(), get_Lef 1
One line is killing my program! 3

Back
Top