Apply a string value to the active cell as a real formula with cell reference

[grexcelman]

I'm halfway to solving this dilemma of mine. I have a table listing dozens of formulas some of which are custom built through a 3rd party API like Bloomberg financial(call that "FormulaTable") while others are more custom designed. A formula can be selected based on two criteria from a data table, "ApplicationTable" where I hope to apply the selected formula given a relative cell value in that table.

Because there's about a dozen different categories in the ApplicationTable, and a given formula choice is dependent on two different field options, an IF statement was not option, nor was a Vlookup. I've solved this part of the problem with an function that uses index match code in vba and named that string variable, "GetMetric" -

Specifically, what I'd like to do is have that string value(the formula chosen from the FormulaTable), get applied to my ApplicationTable as a working formula in column F and apply it with a relative cell reference to column B.

Any ideas how to express a string value as an active formula? I've seen some ideas with evaluate function but couldn't seem to get the formula to work, instead it just printed the string value in the active cell.

 

[grexcelman]

So you code either use the code above, or create a UDF utilising the evaluate function..

Function ConvertTextToFormula(rInput As Range) As Variant
fOutput = Evaluate(rInput.Value)
ConvertTextToFormula = fOutput
End Function

Is that any use at all or am I missing the point?

We're real close I think. This is doing what I wanted except for the cell referenced in the formula. The text that is getting converted will have a cell reference in it and I want to be sure it's referencing the cell relative to its new output range. The cell reference inside the text of the formula needs to be dynamic I suppose so that the converted text can be applied in any row to correctly calculate the cells in its row.

 

[mikerickson]

We're real close I think. This is doing what I wanted except for the cell referenced in the formula. The text that is getting converted will have a cell reference in it and I want to be sure it's referencing the cell relative to its new output range. The cell reference inside the text of the formula needs to be dynamic I suppose so that the converted text can be applied in any row to correctly calculate the cells in its row.

R1C1 notion allows for a static string to change reference depending on where it is put.

 

[grexcelman]

R1C1 notion allows for a static string to change reference depending on where it is put.

Ok. Where would you place that notation? In the text string itself, the code? And how would you express that syntax? If I used this reference for example?

R[0]C[-5]

 

[grexcelman]

Sample code from my failed attempt but hopefully gives a better idea what I'm trying to accomplish: If the formula I retrieved in fOutput is the Sum function, I'd want the formula to add up what value is in the 5th column over.

Function ConvertTextToFormula(rInput As Range) As Variant
fOutput = Evaluate(rInput.Value)
ConvertTextToFormula.FormulaR1C1 = "=""fOutput("" & R[0]C[-5] & "")"""

 

[davesweep]

Maybe there's an easier way..

Assuming you have a function returned to a cell, so the contents of A1 is "SUM"

you could then use VBA to construct the remainder of the formula and apply it:

Sub a()foutput = "=" & Range("A1").Value & "(RC[1]:RC[5])"
Range("B1").FormulaR1C1 = foutput
End Sub

This would give B1 the formula =SUM(C1:G1)

Is this helping?

 

[grexcelman]

Maybe there's an easier way..

Assuming you have a function returned to a cell, so the contents of A1 is "SUM"

you could then use VBA to construct the remainder of the formula and apply it:

Sub a()foutput = "=" & Range("A1").Value & "(RC[1]:RC[5])"
Range("B1").FormulaR1C1 = foutput
End Sub

This would give B1 the formula =SUM(C1:G1)

Is this helping?

Sounded simple enough but I couldn't get that to work. I ended up combining both pieces of code so far into this, also didn't work but is possibly closer to what I'm trying to accomplish. Would prefer to have one single function and code to grab the text, convert it to a formula, and apply it on a relative basis. The other issue I would have is then applying this formula down several hundred rows relative to the cell value in its respective row.

Here's my latest attempt, how would you tweak this?

Function ConvertTextToFormula(rInput As Range) As Variant
foutput = Evaluate(rInput.Value) & "(RC[1]:RC[5])"
ConvertTextToFormula.FormulaR1C1 = foutput

End Function

 

[grexcelman]

Maybe there's an easier way..

Assuming you have a function returned to a cell, so the contents of A1 is "SUM"

you could then use VBA to construct the remainder of the formula and apply it:

Sub a()foutput = "=" & Range("A1").Value & "(RC[1]:RC[5])"
Range("B1").FormulaR1C1 = foutput
End Sub

This would give B1 the formula =SUM(C1:G1)

Is this helping?

Hi Dave, thanks for all your help thus far. I've had to the chance to play with this solution a bit more and it's pretty helpful. The issue I'm facing with this method is that I'll need to somehow run this on nearly 1,000 rows and not all the formulas will be the same, that will depend on a separate lookup function. How can I engineer this to run on a dynamic range(~1,000 rows) of data while avoiding the run time issues associated with a loop and also allow for the flexibilty of different formulas in each row?