I have an array formula that identifies the row number based on multiple criteria, however, I would like to set this value as a variable because I would like to use this as the row number for a different formula.
{=MATCH(J1013&"*"&"55",Workings!K:K&Workings!G:G,0)}
Any ideas how to save the output of this formula as a variable ?
You can use the Evaluate method to evaluate a formula string and output it to a variable in VBA. For example:
Dim vRow
vRow = activesheet.evaluate("MATCH(J1013&""*""&""55"",Workings!K:K&Workings!G:G,0)")
To store intermediate results in formula calculations, you have two options:
1- Add a name in the name manager. i.e
Formulas ---> Name Manager ---> New...
give it a name, such as myVariable and write the formula in the RefersTo box
This creates the variable, you can then refer to it in any formula, like:
= myVar*10
2- dedicate a cell to calculate the formula and refer to the cell.
Option-1 has the advantage of keeping this behind the scenes. Option-2 is more flexible when you want to do the calculation for a whole column, so that the intermediate must be different for each row. In this case, you put your intermediate results in what is called a "helper column".
