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.
DsastrayAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
formular1c1 will return the formula as it was written, in this case D45 is a relative address. FormulaR1C1 would return R45C4 only if your formula had $D$45

Thomas
0
 
DsastrayAuthor Commented:
Ah, makes sense! (In a way that should've been obvious.)
0
 
DsastrayAuthor Commented:
What I mean to say, is your answer was obviously right once you said it, but it had me stumped.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
DsastrayAuthor Commented:
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.
0
 
nutschCommented:
Check out something like this:

Application.ConvertFormula(Selection.FormulaR1C1, xlR1C1, xlR1C1, xlAbsRowRelColumn)

T
0
 
DsastrayAuthor Commented:
Wow, awesome, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.