Explain How Specific R1C1 Formula Works

**chouston** · 04-03-2013, 01:34 PM

I have a bit of code that someone here helped me with a while back, and as my code has evolved, this has stopped working properly, I'm trying to figure out how exactly it works, and what it's doing... Any help is appreciated!

Please Login or Register  to view this content.

**AndyLitch** · 04-03-2013, 01:53 PM

Both of the following are formulas referring to cell A1........

The top one is absolute so R1C1 means row 1 column 1 and it will never change.
Range("E1").FormulaR1C1 = "=R1C1"
So you can copy/paste this formula anywhere on the worksheet but it will always refer to cell A1.

The next sits in E1 and is relative.. R has no number meaning "same row".... C[-4] means 4 columns to the left of the host cell. Host being the cell containing the formula.
Range("E1").FormulaR1C1 = "=RC[-4]"
If you copy/paste this formula it will always refer to 4 colmns left of the host cell

**chouston** · 04-03-2013, 02:15 PM

I think I sort of understand, but I am still struggling to figure out exactly what is going wrong with the code I am working on... If anyone wants to take a look, I would greatly appreciate it! I attached 2 zip files, one with the data output files my code works with and one that it doesn't I also attached the PERSONAL workbook it all runs from. All three files need to be open for the macro to run properly. The code outputs the formula that is causing the issue in lines 25-26, and yes I know my code is not clean at all, this comes from trying to learn VBA while creating this project, sorry if it's confusing at all!