Using FormulaR1C1 - not to set a formula, but to return an existing one
Posted on 2011-05-03
I am interested in double checking formula in one cell against another.
Specifically, I want to make certain that the formula in one cell matches the row reference in other cells in that row.
Obviously, I'm struggling with wording here, so for example:
Formula D5 (the "mother cell") is "=AnotherWorksheet!D45"
I want to confirm that E5, F5, G5 etc. formulas are "=AnotherWorksheet!E45", "=AnotherWorksheet!F45", "=AnotherWorksheet!G45", etc.
And if G5 is "=AnotherWorksheet!G47", it lets me know something is amiss.
(Incidentally, I am also interested in confirming that the column in the cell formula matches the column of the cell.)
My first step is returning the formula in D5. If I use Range("D5").Formula, I get A1 notation ("=AnotherWorksheet!D45").
This seems like sort of a pain to parse (is it?), so I decided to try using Range("D5").FormulaR1C1, but this returns (AFAICT) only relative references ("=AnotherWorksheet!RC").
Is there any way to return "=AnotherWorksheet!R45C4" instead?
As I write this, workarounds are presenting themselves; I do welcome alternative methods, but I am curious if FormulaR1C1 will only return relative references or if it can be coaxed to return absolute references as well.