Excel: VB Macro programming problem with formula writing

[Guest]

I am trying to enter addresses of cells in one worksheet ("source_sheet")
into a matrix of cells in a separate worksheet ("dest_sheet") using a
formula. I am using a nested for loop to fill the appropriate cells in
"dest_sheet". The code works properly in that it writes to the proper cells
in "dest_sheet". It does not work properly in that the formulas that are
written into the cells do not reference the correct addresses.

my write statement inside the for loops is:

Cells(write_row + j, 7).Formula = "='" & source_sheet & "'!r[" & read_col &
"]c[" & read_row & "]"

The resulting addresses refer to the proper sheet ("source_sheet"), but the
addresses of the cells are not correct.

An example formula is:

='raw data 25C'!G3

where 'raw data 25C' is accurate but G3 is not.

Am I misusing the RC cell reference?

HELP!

 

[Guest]

Someone helped me via e-mail:

I was using bracket notation and didn't realize that brackets inferred
relative addressing not absolute addressing.

 

[Jim Rech]

Cells(write_row + j, 7).Formula = "='" & source_sheet & "'!r[" & read_col

& "]c[" & read_row & "]"

Let's take a specific case. Say you're putting a formula in cell C3 and
that read_row is 2 and read_col is 1:

Cells(3, 3).Formula = "='" & source_sheet & "'!r[2]c[1]"

This says you want a cell 2 rows below C3 and 1 column to the right of C3
(aka D5) on the source sheet referenced.

What do you really want?

--
Jim
|I am trying to enter addresses of cells in one worksheet ("source_sheet")
| into a matrix of cells in a separate worksheet ("dest_sheet") using a
| formula. I am using a nested for loop to fill the appropriate cells in
| "dest_sheet". The code works properly in that it writes to the proper
cells
| in "dest_sheet". It does not work properly in that the formulas that are
| written into the cells do not reference the correct addresses.
|
| my write statement inside the for loops is:
|
| Cells(write_row + j, 7).Formula = "='" & source_sheet & "'!r[" & read_col
&
| "]c[" & read_row & "]"
|
| The resulting addresses refer to the proper sheet ("source_sheet"), but
the
| addresses of the cells are not correct.
|
| An example formula is:
|
| ='raw data 25C'!G3
|
| where 'raw data 25C' is accurate but G3 is not.
|
| Am I misusing the RC cell reference?
|
| HELP!
|