none
How to assign the result of a formula to a variable and then use that variable in another formula RRS feed

  • Question

  • Hello.  I have been trying to assign the result of a formula to a variable and then use that variable in another formula, but I just can't seem to get it to work.  I've done searches and found the Evaluate function which I can get to work in other situations, but not with an INDEX/MATCH formula.  This is what I have:

    Dim Prod As Integer

    Prod = Evaluate("INDEX([Inputs.xlsx]Inputs!B1,MATCH(A1,[Inputs.xlsx]Inputs!A1,0))")

    Then, later in the code:

    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<>""NULL"",ROUNDUP(RC[-2]/" & Prod & ",0),IF(RC[-3]<>""NULL"",ROUNDUP(RC[-3]/" & Prod & ",0),56))"

    Any help would be much appreciated!

    Thanks,

    Alex

    Sunday, May 19, 2013 3:54 PM

Answers

  • Hi Alex,

    I suggest trying the Application.WorksheetFunction object instead of evaluate.  This object gives Excel VBA access to all the Excel functions.  

    Below is my rewrite of your code using the WorksheetFunction method.

        Dim Prod As Integer
        
        Prod = Application.WorksheetFunction.Index(Range("B1"), _
            Application.WorksheetFunction.Match(Range("A1"), _
            Range("A1"), 0))
        
        ActiveCell.FormulaR1C1 = _
            "=IF(Not(IsBlank(RC[-2])),Roundup(RC[-2] / " & Prod & ",0)" _
            & ", " _
            & "IF(Not(IsBlank(RC[-3])),ROUNDUP(RC[-3] / " & Prod & ",0),56))"

    If that doesn't work for you please let me know.

    Tom Chandler

    Tuesday, May 21, 2013 12:53 AM

All replies

  • Hi Alex,

    I don't think the index function in a quotation mark will work. Would you please remove the quotation mark and give it a try?

    Prod = Evaluate("INDEX([Inputs.xlsx]Inputs!B1,MATCH(A1,[Inputs.xlsx]Inputs!A1,0))")

    --- >

    Prod = Evaluate(INDEX([Inputs.xlsx]Inputs!B1,MATCH(A1,[Inputs.xlsx]Inputs!A1,0)))

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Monday, May 20, 2013 6:28 AM
    Moderator
  • Hi Yoyo.  Thanks for your response.  If I remove the quotes, I get a "Compile error: Expected: list separator or )".

    Thanks,

    Alex

    Monday, May 20, 2013 6:55 PM
  • Hi Alex,

    I suggest trying the Application.WorksheetFunction object instead of evaluate.  This object gives Excel VBA access to all the Excel functions.  

    Below is my rewrite of your code using the WorksheetFunction method.

        Dim Prod As Integer
        
        Prod = Application.WorksheetFunction.Index(Range("B1"), _
            Application.WorksheetFunction.Match(Range("A1"), _
            Range("A1"), 0))
        
        ActiveCell.FormulaR1C1 = _
            "=IF(Not(IsBlank(RC[-2])),Roundup(RC[-2] / " & Prod & ",0)" _
            & ", " _
            & "IF(Not(IsBlank(RC[-3])),ROUNDUP(RC[-3] / " & Prod & ",0),56))"

    If that doesn't work for you please let me know.

    Tom Chandler

    Tuesday, May 21, 2013 12:53 AM