Trying to give a cell a formula

[Jan Nordgreen]

I use WinXP and Excel 2000.

In a program I would like set this:

='C:\jans data\jan programming\excel\administracion velarde\[Expensas marzo
2004.xls]PL general'!$M10

as a cell's formula.

The cell links to a cell in another workbook.

I have tried:

Worksheets("Resumen").Cells(4, 3 * i).FormulaR1C1 = "='" & ccarpeta & "[" &
cfilename & "]PL general'!$M10"

where ccarpeta = "C:\jans data\jan programming\excel\administracion
velarde\"
and cfilename = "Expensas marzo 2004.xls"

but I get runtime error '1004'.

It seems it doesn't like the apostrophes ('), which is strange since it does
accept them when I copy and paste the string into the formula bar by hand.

I have tried many other string variations, but with no luck.

When I try: Worksheets("Resumen").Cells(4, 3 * i).FormulaR1C1 = "=" &
ccarpeta,
I get "=I:I:\jans data\jan programming\excel\administracion velarde\". Where
did those Is come from?

Appreciate any help on this!

Regards,

Jan Nordgreen

 

[Jan Nordgreen]

The trick was to use

Worksheets("Resumen").Cells(4, 3 * i).Formula

instead of

Worksheets("Resumen").Cells(4, 3 * i).FormulaR1C1

Puuuh!

 

[Vasant Nanavati]

Have you tried using Formula instead of FormulaR1C1?