Unable to assign FormulaR1C1 correctly

[Geoff]

The following lines create a formula (R1C1 style) and assign it to a range of
cells (please note I have edited the name used in this snippet because of the
sensitivity of the data involved):

cellForm = "='" & cjBkLast.Path & "[" & cjBkLast.Name & "]WORKSHEET_NAME'!RC9"
..Range("N8:N13, N18:N24, N26:N30").FormulaR1C1 = cellForm

The cellform variable has this value in the Locals window:
"='P:\WORKBOOK_PATH\Testing\CJ\2008[CJ01_08.xls]WORKSHEET_NAME'!RC9"

However, on running this code the formula in the cells comes out as the
following
='P:\WORKBOOK_PATH\Testing\CJ\[2008[CJ01_08.xls]WORKSHEET_NAME]2008[CJ01_08.xls]PARTIAL_WORKSHEET_NAME'!$I8

Basically the formula is being mangled, and I have no idea why - the same
method of assigning formulae to cells has worked fine in other places in the
same sub. Any help or insight would be greatly appreciated.

 

[Dave Peterson]

You lost a backslash:

CellForm = "='" & cjBkLast.Path & "\[" & cjBkLast.Name & "]WORKSHEET_NAME'!RC9"

The following lines create a formula (R1C1 style) and assign it to a range of
cells (please note I have edited the name used in this snippet because of the
sensitivity of the data involved):

cellForm = "='" & cjBkLast.Path & "[" & cjBkLast.Name & "]WORKSHEET_NAME'!RC9"
.Range("N8:N13, N18:N24, N26:N30").FormulaR1C1 = cellForm

The cellform variable has this value in the Locals window:
"='P:\WORKBOOK_PATH\Testing\CJ\2008[CJ01_08.xls]WORKSHEET_NAME'!RC9"

However, on running this code the formula in the cells comes out as the
following:
='P:\WORKBOOK_PATH\Testing\CJ\[2008[CJ01_08.xls]WORKSHEET_NAME]2008[CJ01_08.xls]PARTIAL_WORKSHEET_NAME'!$I8

Basically the formula is being mangled, and I have no idea why - the same
method of assigning formulae to cells has worked fine in other places in the
same sub. Any help or insight would be greatly appreciated.

 

[Geoff]

And you found it for me. Many thanks Dave, I thought I was going mad!
PEBKAC...
--
There are 10 types of people in the world - those who understand binary and
those who don't.

You lost a backslash:

CellForm = "='" & cjBkLast.Path & "\[" & cjBkLast.Name & "]WORKSHEET_NAME'!RC9"

The following lines create a formula (R1C1 style) and assign it to a range of
cells (please note I have edited the name used in this snippet because of the
sensitivity of the data involved):

cellForm = "='" & cjBkLast.Path & "[" & cjBkLast.Name & "]WORKSHEET_NAME'!RC9"
.Range("N8:N13, N18:N24, N26:N30").FormulaR1C1 = cellForm

The cellform variable has this value in the Locals window:
"='P:\WORKBOOK_PATH\Testing\CJ\2008[CJ01_08.xls]WORKSHEET_NAME'!RC9"

However, on running this code the formula in the cells comes out as the
following:
='P:\WORKBOOK_PATH\Testing\CJ\[2008[CJ01_08.xls]WORKSHEET_NAME]2008[CJ01_08.xls]PARTIAL_WORKSHEET_NAME'!$I8

Basically the formula is being mangled, and I have no idea why - the same
method of assigning formulae to cells has worked fine in other places in the
same sub. Any help or insight would be greatly appreciated.

 

[Dave Peterson]

Sometimes, it's good to build a formula manually (with the sending workbook
open) right in a cell. Then close the sending workbook and compare it to the
string you create in the code.

That's what I did <vbg>.

And you found it for me. Many thanks Dave, I thought I was going mad!
PEBKAC...
--
There are 10 types of people in the world - those who understand binary and
those who don't.

You lost a backslash:

CellForm = "='" & cjBkLast.Path & "\[" & cjBkLast.Name & "]WORKSHEET_NAME'!RC9"

The following lines create a formula (R1C1 style) and assign it to a range of
cells (please note I have edited the name used in this snippet because of the
sensitivity of the data involved):

cellForm = "='" & cjBkLast.Path & "[" & cjBkLast.Name & "]WORKSHEET_NAME'!RC9"
.Range("N8:N13, N18:N24, N26:N30").FormulaR1C1 = cellForm

The cellform variable has this value in the Locals window:
"='P:\WORKBOOK_PATH\Testing\CJ\2008[CJ01_08.xls]WORKSHEET_NAME'!RC9"

However, on running this code the formula in the cells comes out as the
following:
='P:\WORKBOOK_PATH\Testing\CJ\[2008[CJ01_08.xls]WORKSHEET_NAME]2008[CJ01_08.xls]PARTIAL_WORKSHEET_NAME'!$I8

Basically the formula is being mangled, and I have no idea why - the same
method of assigning formulae to cells has worked fine in other places in the
same sub. Any help or insight would be greatly appreciated.