So I've got an excel workbook. I've got a module where I input the code below.



I run the macro, and Lo and behold, praise the lord, a miracle occurs, and in my spreadsheet, I get things like this:



Now I'm totally ok with RC[] returning cell references, but getting 'N7' instead of N7 is breaking my reference. What is going on?

Jason

Jason

Your mixing R1C1 and A1 referencing when trying to create a R1C1 reference using the FormulaR1C1 property.

What should N7 be in R1C1 referencing?

Ah yes. I guess I am.

I tried using the R1C1 reference, but it's supposed to be a nonchanging reference. Actually I would want it to be $N$7.

the thing is, this formula gets mass pasted, so the relative references are important as well. Can I get one absolute reference and one relative reference?


Jason

So the solution I found was to declare a variable, set the variable equal to the absolute cell reference, and concatenate the variable into the FormulaR1C1 expression.

"=IF(RC[13]>0,MIN(ROUNDDOWN(" & NewVariable & "/RC[16],0),RC[13]))"

This worked for me.

I think the key to absolute/relative referencing in R1C1 notation is the [].

By the way the R in RC[13] is being treated as column R.