FormulaArray - interpolate variables

  • 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
    1. Range("I49").Select
    2. Selection.FormulaArray = _
    3. "=SUM((retrieved_data!F2:F5000=""client9"")*(retrieved_data!J2:J5000=""answered9""))"
    4. answered9val = Range("I49").Value


    Appreciate help,


    /Nick

    <b><font color="#23069E">_NFio_</font></b>

  • Re: FormulaArray - interpolate variables


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


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

    Boo!:yikes:

  • Re: FormulaArray - interpolate variables


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


    Code
    1. Selection.FormulaArray = _
    2. "=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]

  • Re: FormulaArray - interpolate variables


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

    <b><font color="#23069E">_NFio_</font></b>