# Anchoring cells in R1C1 format

#### TheBlueMusketeer

##### New Member
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

1,081,516
Messages
5,359,232
Members
400,523
Latest member
ExcelNewbie98

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...