Using variable names in formula

  • Thread starter Thread starter Karen M.
  • Start date Start date
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.
 
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 :
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use count data in formula 1
Use cell contents to make formula reference 4
use of variable in array formula 1
use a variable for a worksheet name in a formula 3
Use external file variable in formula 2
Display value of a VBA variable inside a cell 2
Use Value of Dynamically Created Variable 5
VBA to run all variable combinations in a calculation model and store result 6

Back
Top