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

 

[jindon]

try

SUBSTITUTE(RC[-" & nc & "]

 

[Rhino_Dance]

Thanks. I tried that and got the message:

Compile error: Expected end of statement and the red part below was highlighted as the problem.

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

(Maybe it some how 'coupled' the first 2 quotation marks and couldn't make sense of the rest?)

Then I took the 2 quotes off and just had the &c2& but got the error:
'Application Defined or Object Defined Error'

Also tried ""&nc&"" which gave the same error as above.

BTW, my Dim statment is:

Dim nc As Integer

Which should be fine... right? Thanks again.

 

[jindon]

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

 

[Rhino_Dance]

Thanks. You're right about the nc. The BoxPipe1 needed no delimiters though. Formula is:


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

Thanks very much jindon- you really helped me out on this one!

 

[dvr019]

Hi All:

I would like to extend the above option to include the $ to fix whatever the column is calculated using nc. Lets say, it generates a formula like F42, I need $F42.

Any ideas?

Regards
DVR

 

[Andrew Poulsom]

Welcome to MrExcel.

This:

RC[-" & nc & "],

would be:

RC" & nc & ",

 

[dvr019]

It seems there is an issue it simply take the cell and then puts the variable value in it. I gives a new formula, which is not intended.

DVR

 

[Andrew Poulsom]

What VBA code are you using exactly? And what outcome do you expect?

 

[dvr019]

I am actually looking for a code where a cell is updated with a formula consisting of R & C details.
I mean the row number or colum number are caluclated based on other formula.
Finally, the vba code should give me something like R[variable]C[variable], where variable is calculated based on other formula. This I could get without any problems. However, I also need a dollar promt $ infront of column once the code runs.

That I could not get it. When I tried your earlier suggestion, it is simply keeping the variable value within the formula.

regards
DVR