ActiveCell.FormulaR1C1 Dynamic Row

**goldenbear10** · 12-07-2009, 03:29 AM

Hi,

I am trying to use ActiveCell.FormulaR1C1 to select a particular cell.

My code is the following:

ActiveCell.FormulaR1C1 = "='MySheet'!R[9]C"

However, I don't always want it to select 9 rows down.
Earlier in the macro I have defined the following variables:
assumptions_end
assumptions_beg

I tried to use the following syntax but it did not work:

ActiveCell.FormulaR1C1 = "='MySheet'!R[assumptions_end - assumptions_beg]C"

What is the proper syntax for using a dynamic row? (The column does not need altering).

**DonkeyOte** · 12-07-2009, 03:32 AM

If relative use:

ActiveCell.FormulaR1C1 = "='MySheet'!R[" & assumptions_end - assumptions_beg & "]C"

If non-relative (ie absolute/fixed row) remove the [ ]

**goldenbear10** · 12-07-2009, 04:40 AM

Thanks. The non-relative is really the only way for me to do what I need, but is there any way to remove the "$" that comes along with the non-relative formula? The formula fixes the row and I would like to undo that, if possible.

**DonkeyOte** · 12-07-2009, 04:47 AM

Originally Posted by goldenbear10

Thanks. The non-relative is really the only way for me to do what I need, but is there any way to remove the "$" that comes along with the non-relative formula? The formula fixes the row and I would like to undo that, if possible.

I'm afraid that doesn't make much sense to me - if you don't want the $ then that is not a "non-relative" approach.

There are quite a few posts where people will outline the basics re: R1C1 notation - "here is one I made eariler" so to speak:

http://www.excelforum.com/2171545-post2.html