Anchoring cells in R1C1 format

[TheBlueMusketeer]

Hello,

Consider the following line of code:

Cells(2,10).FormulaR1C1 = "=RC[-2]*RC[-1]/R[40]C[-2]"

I want to be able to anchor the R[40]C[-2] part as I will be dragging this formula down the sheet and I don't want the divisor's cell reference to change. Is there a way to accomplish this?

I was able to get around this problem by using the value in cell R[40]C[-2] in the formula instead but was wondering if anybody knew how to anchor relative references.

Thanks!

 

[TheBlueMusketeer]

I'm using Excel 2007 in Windows XP. I tried to Google this for almost an hour but could not find similar questions asked anywhere else.

Regards,
TheBlueMusketeer

 

[AlphaFrog]

Hi and welcome to the forum.

Do you know that you want to reference H42. If yes then...
Cells(2, 10).FormulaR1C1 = "=RC[-2]*RC[-1]/R42C8"

If not then you can do the offset math like this...
Cells(2, 10).FormulaR1C1 = "=RC[-2]*RC[-1]/R" & 40 + 2 & "C" & 10 - 2

Or you could use a named range for H42
Cells(2, 10).FormulaR1C1 = "=RC[-2]*RC[-1]/MyNamedRange"

 

[TheBlueMusketeer]

Hi and welcome to the forum.

Do you know that you want to reference H42. If yes then...
Cells(2, 10).FormulaR1C1 = "=RC[-2]*RC[-1]/R42C8"

If not then you can do the offset math like this...
Cells(2, 10).FormulaR1C1 = "=RC[-2]*RC[-1]/R" & 40 + 2 & "C" & 10 - 2

Or you could use a named range for H42
Cells(2, 10).FormulaR1C1 = "=RC[-2]*RC[-1]/MyNamedRange"

Thanks a lot AlphaFrog! The 2nd formula is what I was looking for as a set of columns get added to my sheet every day. Instead of having Cells(2,10) I actually have Cells(2,LastCol+1) where LastCol=Rows(2).Cells.Find("*",SearchOrder:=xlbyColumns, SearchDirection:=xlPrevious).Column

I'm so glad to see the $ anchors in the divisor. The formula did not look elegant with the value of the divisor in it before - now it does.

Thanks again for the quick response!

Regards,
TBM