Anchoring cells in R1C1 format

TheBlueMusketeer

New Member
Joined
Sep 29, 2012
Messages
12
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
Joined
Sep 29, 2012
Messages
12
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
Joined
Sep 2, 2009
Messages
16,166
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
Joined
Sep 29, 2012
Messages
12
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
 

Forum statistics

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

Some videos you may like

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...
Top