Using variable names in formula

K

Karen M.

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.
 
K

keepITcool

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 :
 
Top