• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

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.
0
Dsastray
Asked:
Dsastray
  • 4
  • 2
1 Solution
 
nutschCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now