CODEWITHSUNDEEP
vbaexcelexcel-formula
sunstache32
sunstache32

Reputation: 149

Evaluate String as Formula

As part of a longer piece of code, I've made a formula into a string. Now I want to go down a column and place that formula into each cell. I am running into problems with getting the VBA code to evaluate my string as a formula. When I try to evaluate the formula like this, I get a type mismatch error at the line where I try to evaluate. Here's the part I'm having trouble with:

Dim commandstring As String

commandstring = [{=INDEX(" & firstArgument & ",MATCH(1,(" & secondArgument & "=" & condition1 & ")*(" & thirdArgument & "=" & condition2 & ")*(" & patid1 & "=" & condition3 & "),0))}]

I read that putting the square brackets around the formula will evaluate it, but I can't get it work. Any help is appreciated, Thanks!

Upvotes: 1

Views: 2807

Answers (3)

Sanias
Sanias

Reputation: 53

I wanted to piggyback off of ManishChristian's answer and recommend using 

Function Eval(Ref As String)
    Application.Volatile
    Eval = Application.Evaluate(Ref)
End Function

Omitting "Application." before Evaluate gave me the following error:

Automation Error: Catastrophic Failure

and corrupted my file. I'm not sure if omission correlates to error, but at least in certain applications it will so it is best to be safe.

Upvotes: 0

MatthewD
MatthewD

Reputation: 6801

Loop through and put the formula in to the cell formula. Here the string is fixed.

commandstring  = "{=INDEX(" & firstArgument & ",MATCH(1,(" & secondArgument & "=" & condition1 & ")*(" & thirdArgument & "=" & condition2 & ")*(" & patid1 & "=" & condition3 & "),0))}"


Dim lRow As Long
Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
lRow = 1

'Loop through and put the formula in each cell
Do While lRow <= ws.UsedRange.Rows.count

    ws.Range("B" & lRow).Formula = "{=INDEX(" & firstArgument & ",MATCH(1,(" & secondArgument & "=" & condition1 & ")*(" & thirdArgument & "=" & condition2 & ")*(" & patid1 & "=" & condition3 & "),0))}"

    lRow = lRow + 1
Loop

Or if you really want to use it in a string variable.

ws.Range("B" & lRow).Formula = commandstring

Upvotes: 1

ManishChristian
ManishChristian

Reputation: 3784

Try this small function which takes a string as a formula: 

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

Call to this function: 

Dim commandstring As String

commandstring = "{=INDEX(" & firstArgument & ",MATCH(1,(" & secondArgument & "=" & condition1 & ")*(" & thirdArgument & "=" & condition2 & ")*(" & patid1 & "=" & condition3 & "),0))}"

commandstring = Eval(commandstring)

Upvotes: 1

Related Questions