+ Reply to Thread
Results 1 to 4 of 4

Convert string to formula

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    9

    Convert string to formula

    Hi,

    I have string like this.. built from "&" and the output is 'G:\Crush\Daily plant reports\2019\[05-2019.xls]C vol'!$F$35. When I use the named variable as Eval=IF(NOW(),EVALUATE('G:\Crush\Daily plant reports\2019\[05-2019.xls]C vol'!$F$35)) and I call this named variable in another cell =Eval then I get #REF error.

    I wrote the VBA function as
    Please Login or Register  to view this content.
    And calling this function in a cell as myEvaluate('G:\Crush\Daily plant reports\2019\[05-2019.xls]C vol'!$F$35) it gives me #VALUE error.

    Any help?
    Last edited by cndesu; 06-25-2019 at 11:40 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Convert string to formula

    Code tags now added. Thanks
    Last edited by Pepe Le Mokko; 06-26-2019 at 02:40 AM.

  3. #3
    Registered User
    Join Date
    12-24-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Convert string to formula

    Thanks, I forgot to add the code tags around the code.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Convert string to formula

    What is in F35 of the other file?

    You can only evaluate a formula in a string format, anything else in F35 will give an error.

    To elaborate myEvaluate('G:\Crush\Daily plant reports\2019\[05-2019.xls]C vol'!$F$35) will try to evaluate the content of F35 as a formula, while myEvaluate("'G:\Crush\Daily plant reports\2019\[05-2019.xls]C vol'!$F$35") will evaluate the string within the function and return the content of F35.

    Of course, this assumes that the path, filename and sheetname are all valid.

    Also the use of Application.Volatile in the function code makes =IF(NOW() redundant.
    Last edited by jason.b75; 07-03-2019 at 07:58 AM.

+ 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] INDIRECT returning #REF error when trying to convert string to formula
    By cwyn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2013, 08:15 PM
  2. [SOLVED] Formula to convert string
    By rizmomin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2013, 08:57 PM
  3. [SOLVED] Formula to convert string in a cell to a date
    By maryren in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-20-2013, 12:08 PM
  4. [SOLVED] Need to Convert Formula R1C1 into A1-style but the Formula String exceeds 255 characters
    By VBA_Gary in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-04-2012, 12:09 PM
  5. [SOLVED] convert string to formula
    By MartyZ in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-09-2012, 10:43 AM
  6. Convert string into a cell reference in a formula
    By seaottr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2011, 07:10 PM
  7. convert array formula output to string
    By MCCCLXXXV in forum Excel General
    Replies: 2
    Last Post: 07-07-2011, 11:57 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