ExcelBanter - Excel Equation Help

jflatt95 wrote:
I have data in column C, that has to be processed through one of 3 possible
equations (which is inputted in column D). In column D, I am using VLOOKUP to
look at column C, and then pull the right equation into column D. However, my
issue is that my equations are set something like (=0.0456*x). So in column E
instead of getting my values, I just get the equation.

I would like to know how I can get column D, to pull in the data it needs
(ie the value of x) from column C (ie the data in column C = x (in the
equation)).

So what I should have then is

Column C Column D Column E (Value from the
chosen
Data Which equation to use equation and data)

I think VBA is the only way to get this done. Here's a UDF that will do it.

Paste the code below in a standard module and call function DoEval in
your worksheet like this:

=DoEval(D2,TRUE,"x",C2)

Here's how the parameters work:

1 Expr) The formula you want to evaluate. I.e., what you have in column D.
2 TrimOutside) Specifies whether to trim off the first and last
characters from the formula.
If your formula has surrounding punctuation that will confuse Excel's
evaluation engine, e.g., (=0.0456*x) specify TRUE here.
If your formula looks like a proper Excel formula, e.g., =0.0456*x,
specify FALSE.
3 and 4 P) Must be entered in pairs. The first element of each pair is
the "variable" in your formula ("x"), the second element is the value
you want to replace it with (cell C2). You can enter an unlimited number
of such pairs.

This is not bullet-proof, but hopefully you find it useful.

Here's how the UDF can be used with two pairs of substitutions for P:
B3: 0.05
C3: 0.7
D3: '=sin(x)+sin(y)
E5: =DoEval(D3,FALSE,"x",B3,"y",C3)
(result: 0.694196857)

' BEGIN CODE ----------------------------------------------------
Public Function DoEval( _
Expr As String, _
TrimOutside As Boolean, _
ParamArray P() As Variant) As Variant

Dim i As Long
Dim L As Long

L = UBound(P)
If L Mod 2 < 1 Then
' incorrect number of parameters -- must be a pairwise list
DoEval = "#N/A"
Exit Function
End If

If TrimOutside Then
Expr = Mid(Expr, 2, Len(Expr) - 2)
End If

Do While i < L
Expr = Replace(Expr, P(i), P(i + 1))
i = i + 2
Loop

DoEval = Evaluate(Expr)
End Function
' END CODE ----------------------------------------------------