Using FormulaR1C1 - not to set a formula, but to return an existing one
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!R[40]C
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.
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!R[40]C
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What I mean to say, is your answer was obviously right once you said it, but it had me stumped.
ASKER
We got the smaller question answered, and I think just typing it out gave me lots of leads on how to approach my bigger problem.
Check out something like this:
Application.ConvertFormula
T
Application.ConvertFormula
T
ASKER
Wow, awesome, thanks!
ASKER