Using variable names in formula
 

I have a formula that is entered by VBA.

It obtains parameters from a dialog that is displayed to
the user.

Formula entered manually would be:
Range("A8").FormulaArray = "=PISampDat(Parameters!
R2C6,""*-12h"",""*"",""1m"", 1,""PIPV"")"

Formula entered by VBA needs to have the variable names:
12h would be a concantenation of two variables...
txtDuration.Value & Combobox3.Value

1m would also be a concantenation of two variables...
txtInterval.Value & Combobox2.Value

How do I substitute the variable names into the formula
mess.

Using variable names in formula
 

Karen,

when it gets complicated with all the double quotes etc..
this may not be ultra fast, but it will keep your code readable.


Const FML = _
"=PISampDat(Parameters!R2C6,""*-12h"",""*"",""1m"", 1,""PIPV"")"
Dim strH$,strM$

strH = txtDuration.Value & Combobox3.Value
strM = txtInterval.Value & Combobox2.Value

Range("a8").FormulaArray = Replace(Replace(FML,"12h",strH),"1m",strM)

if you have xl97 use application.substitute iso Replace


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Karen M. wrote :

I have a formula that is entered by VBA.

It obtains parameters from a dialog that is displayed to
the user.

Formula entered manually would be:
Range("A8").FormulaArray = "=PISampDat(Parameters!
R2C6,""*-12h"",""*"",""1m"", 1,""PIPV"")"

Formula entered by VBA needs to have the variable names:
12h would be a concantenation of two variables...
txtDuration.Value & Combobox3.Value

1m would also be a concantenation of two variables...
txtInterval.Value & Combobox2.Value

How do I substitute the variable names into the formula
mess.