How to use VBA write formula

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
 
B

Bernie Deitrick

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
 
Top