Using FormulaR1C1 - not to set a formula, but to return an existing one

Posted on 2011-05-03
Last Modified: 2012-05-11
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.
Question by:Dsastray
    LVL 39

    Accepted Solution

    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


    Author Comment

    Ah, makes sense! (In a way that should've been obvious.)

    Author Comment

    What I mean to say, is your answer was obviously right once you said it, but it had me stumped.

    Author Closing Comment

    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.
    LVL 39

    Expert Comment

    Check out something like this:

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


    Author Comment

    Wow, awesome, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now