+ Reply to Thread
Results 1 to 5 of 5

Using worksheet FORMULATEXT function in VBA

  1. #1
    Registered User
    Join Date
    11-18-2011
    Location
    Dorset
    MS-Off Ver
    Excel 365
    Posts
    37

    Using worksheet FORMULATEXT function in VBA

    For example...
    I can use e.g.

    =FORMULATEXT(A9)

    in a spreadsheet, and the results can be either

    #N/A

    or e.g.

    ='E-W Cross-section'!F7

    O.K.

    But when I come to use that in VBA, thus -

    YourTest = WorksheetFunction.FORMULATEXT(myCell)

    I don't know how to use that result, in e.g.

    If YourTest = ???? Then

    Question :-

    What do I use in that expression to represent
    #N/A and
    #VALUE!
    (any others ?)

    Pretty please ?


    OH !

    ... and now I find that the Line I gave above -

    YourTest = WorksheetFunction.FORMULATEXT(myCell)

    ... doesn't actually work !


    What I am TRYING to achieve is this -
    a) Create a spreadsheet
    b) Populate it with some "input" - numbers and text
    c) add some calculations (and graphs) using those input figures.

    Then -
    d) create a copy of that Sheet, so I now have "OldSheet" and "NewSheet"

    And now the bit that I'm having difficulty with -
    e) Run through the NewSheet, and -
    If the Cell is empty, ignore it;
    if it contains an equation, ignore it;
    if it contains a reference to another Cell, ignore it
    if it contains a reference to another Sheet, ignore it
    BUT - if it contains input ("raw data"), replace that with a reference to the OldSheet

    This means that the data is only input once, no matter how many copies of the sheet are made.
    Last edited by RobinClay; 05-31-2019 at 02:01 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Processing Errors

    FORMULATEXT does not appear to be available in WorksheetFunction but it is more direct to use the Formula property of the cell.

    Please Login or Register  to view this content.
    You will never get #N/A or #VALUE or other errors, because you are getting the formula itself, not the result of the formula. FORMULATEXT returns #N/A if the cell is blank, but .Formula returns a blank if the cell is blank.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Processing Errors

    If you simply want to check if a cell has a formula, try
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-18-2011
    Location
    Dorset
    MS-Off Ver
    Excel 365
    Posts
    37

    Re: Processing Errors

    Thank you both very much indeed for your replies - AND for the swiftness thereof !

    I *think* / HOPE that solves my "challenge".
    I've sweated over it for two days !

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Using worksheet FORMULATEXT function in VBA

    With the added explanation in your op, try
    Please Login or Register  to view this content.
    and thanks for the Rep
    Last edited by Fluff13; 05-31-2019 at 03:00 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] FORMULATEXT Function Equivalent for 2007
    By TheMechEngineer in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-23-2020, 05:20 PM
  2. [SOLVED] Circular Reference with HYPERLINK and FORMULATEXT: why?
    By Requin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2017, 11:07 AM
  3. ?Offset and formulatext functions?
    By atung in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-19-2016, 01:03 PM
  4. [SOLVED] How to make the Worksheet search function look at the next worksheet if user click no
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2014, 09:10 PM
  5. Turning a function from serving current worksheet to specific worksheet
    By ahmadmasri in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-15-2013, 10:15 PM
  6. Using date format and left function with worksheet name to hide worksheet
    By rgullett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-21-2012, 05:16 PM
  7. Replies: 3
    Last Post: 02-21-2010, 12:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1