RC Notation using a variable?

[Rhino_Dance]

The following vba line successfully inserts a Substitute Formula into one cell that refers to another cell 46 columns to the left:

ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-46],BoxPipe2,""|"")"

I want to replace the 46 with a numeric variable called 'nc'. But I get an error when I try to run the line which is shown below. The variable is definitely equal to 46. Can someone tell me why this doesn't work?

ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-nc],BoxPipe1,""|"")"


(Also I'm using SUBSTITUTE because the CELLS.REPLACE method does not always work for this case due to the imported characters I'm replacing.)

Thanks

 

[Andrew Poulsom]

To make the row or column absolute omit the square brackets [].

 

[Rini88]

Hi all,

I have a similar problem and I am not sure how to solve it. I would like to use the number of a certain row (changing, depending of length of the file) to use a reference to go to in another sheet. So basically I want to go to the same row in another sheet but don't know which row that is.

Assume that I have found the starting cell in row 21 on my first sheet and B21 is selected.

Dim var_row As Integer var_row = "=ROW()"
Rows(ActiveCell.Row).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Cells(1, var_row).Select

But already the second line returns Run-time error '13': Type mismatch

Could anyone point out what I need to change in order to make this work? Much appreciated

 

[vb1der]

try

SUBSTITUTE(RC[-" & nc & "]

1) remember that the "&" needs a space before it as vba can confuse the notation as a data type.

2) I am also used to passing scripts across unix systems in my other work so double quotes always makes me nervous so when i need a quote I will do something like ..sdsf" & chr(34) & variable & chr(34) & '....rest of the text.

 

[Jonmo1]

However, I also need a dollar promt $ infront of column once the code runs.

Remove the [] brackets to get $ in the reference.

R1C1 = Absolute = $A$1
R[1]C[1] = Relative = A1
R1C[1] = Row Absolute, Col Relative = $A1
R[1]C1 = Row Relative, Col Absolute = A$1

The difference with absolute vs relative.
Absolute, you need your variable to be the actual column #, A=1 B=2 C=3 etc
Relative, you need your variable to be a number 'away from' the cell the formula is entered in.

 

[Jonmo1]

Also, just FYI.
It's possible and quite easy to reference cells with column #s
So you can use .Formula instead of .FormulaR1C1

Cells(Row#,Column#).Address(RowAbsolute,ColAbsolute)

Cells(1,1).Address(0,0) will produce A1
Cells(5,7).Address(1,0) will produce G$5
Cells(23,2).Address(0,1) will produce $B23
Cells(5,5).Address(1,1) will produce $E$5