Answered by:
How to assign the result of a formula to a variable and then use that variable in another formula
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
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
 Marked as answer by Dummy yoyoModerator Monday, June 3, 2013 11:00 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.
 Edited by Dummy yoyoModerator Monday, May 20, 2013 6:29 AM


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
 Marked as answer by Dummy yoyoModerator Monday, June 3, 2013 11:00 AM