FormulaArray - interpolate variables

_NFio_ · Nov 25th 2004

In the code below I cannot seem to get the variables of client9 and answer9 to interpolate.
I've tried using "& client9" and "& client9 &" among many other ways, but I still get either an error or the actual string 'client9' and not it's value

client9 and answered9 are dim as strings
and assigned values earlier in the code

Code

Range("I49").Select


Selection.FormulaArray = _
    "=SUM((retrieved_data!F2:F5000=""client9"")*(retrieved_data!J2:J5000=""answered9""))"


answered9val = Range("I49").Value

Appreciate help,

/Nick

**norie** · Nov 25th 2004

Re: FormulaArray - interpolate variables

I'm not 100% sure what you mean but does this work:

Code

Selection.FormulaArray = _
"=SUM((retrieved_data!F2:F5000=" & Chr(34) & client9 & Chr(34) & _
")*(retrieved_data!J2:J5000=" & Chr(34) & answered9 & Chr(34) & "))"

tinyjack · Nov 25th 2004

Re: FormulaArray - interpolate variables

The problem you have is that you need the quotes and the variable, so:

Code

Selection.FormulaArray = _ 
"=SUM((retrieved_data!F2:F5000=""" & client9 & """)*(retrieved_data!J2:J5000=""" & answered9 & """))"

Should do the trick.

TJ

[edit]Too slow, even though I used "" instead of Chr(34) and I only do that because it is quicker to type :)[/edit]

_NFio_ · Nov 26th 2004

Re: FormulaArray - interpolate variables

thanks !! It never occured to use the ansi chr or add another pair of quotes, appreciate it - works great now

FormulaArray - interpolate variables

Participate now!

Share