How to use VBA write formula

  • Thread starter Thread starter steve.wang
  • Start date Start date
S

steve.wang

Hello Guys,
I want to set a range value to a formula which reference
to another closed workbook range. but, I don't know how?

example:
rng.formulaR1C1="=vlookup("+search+",rng in [another
closed workbook]sheetname!"+"r9c9, 9)"

thanks
 
Steve,

Along the lines of:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(" & Search & ",'C:\FolderName\" & _
"[WorkbookName.xls]SheetName'!R1C1:R9C9,9,FALSE)"

The R1C1:R9C9 means A1:I9...

The easiest way to get the syntax correct is to open both workbooks, make up
your formula so that it works, then close the workbook without the formula.
Then start the macro recorder, select the cell with the formula, press F2,
then press Enter. Turn off the recorder, and take a look at your code.

HTH,
Bernie
MS Excel MVP
 
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

Excel VBA 1
Error using..... .Value = .Value 4
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
Code does not error but no copy either 4
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7
End process at last row of data 3
R1C1 to A1 inaccurate formula conversion 2
Save As and new workbook new name used in existing code 8

Back
Top