Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel: VB Macro programming problem with formula writing
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel: VB Macro programming problem with formula writing
Someone helped me via e-mail:
I was using bracket notation and didn't realize that brackets inferred relative addressing not absolute addressing. "d-cubed" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel: VB Macro programming problem with formula writing
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 "d-cubed" wrote in message ... |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! | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem writing formula | Excel Programming | |||
Problem writing formula | Excel Worksheet Functions | |||
Formula writing problem | Excel Worksheet Functions | |||
VB Macro writing a formula to a cell | Excel Programming | |||
macro programming problem | Excel Programming |