I am a novice in programming excel. I am trying to insert formula
through macro in a cell by using Formulaarray. I am encountering a
peculiar problem.

Formula i want to insert is

=-IF(UPPER($EC$14)="Y",AH22*AI22,AN22)*R22*SUMIF(PAY MENT_INDEX,"="
&F22,RNG_OPT_PREM)/COUNTIF(PAYMENT_INDEX,&F22)

Now if i want to insert it in A1 say,

i would write

A1.formulaArray =
"=-IF(UPPER($EC$14)="Y",AH22*AI22,AN22)*R22*SUMIF(PAY MENT_INDEX,"="
&F22,RNG_OPT_PREM)/COUNTIF(PAYMENT_INDEX,&F22)"

But VBA recognizes the starting inverted commas of the "Y" as the end
of formula and gives me syntax error.
How do i overcome this?

Thanks

I think you can do this by using double double quotes inside the formula array

eg.
A1.formulaArray =
"=-IF(UPPER($EC$14)=""Y"",AH22*AI22,AN22)*R22*SUMIF(P AYMENT_INDEX,""=""&F22,RNG_OPT_PREM)/COUNTIF(PAYMENT_INDEX,&F22)"

" wrote:

I am a novice in programming excel. I am trying to insert formula
through macro in a cell by using Formulaarray. I am encountering a
peculiar problem.

Formula i want to insert is


=-IF(UPPER($EC$14)="Y",AH22*AI22,AN22)*R22*SUMIF(PAY MENT_INDEX,"="
&F22,RNG_OPT_PREM)/COUNTIF(PAYMENT_INDEX,&F22)

Now if i want to insert it in A1 say,

i would write

A1.formulaArray =
"=-IF(UPPER($EC$14)="Y",AH22*AI22,AN22)*R22*SUMIF(PAY MENT_INDEX,"="
&F22,RNG_OPT_PREM)/COUNTIF(PAYMENT_INDEX,&F22)"

But VBA recognizes the starting inverted commas of the "Y" as the end
of formula and gives me syntax error.
How do i overcome this?

Thanks

" wrote:

I am a novice in programming excel. I am trying to insert formula
through macro in a cell by using Formulaarray. I am encountering a
peculiar problem.

Formula i want to insert is

=-IF(UPPER($EC$14)="Y",AH22*AI22,AN22)*R22*SUMIF(PAY MENT_INDEX,"="
&F22,RNG_OPT_PREM)/COUNTIF(PAYMENT_INDEX,&F22)

Now if i want to insert it in A1 say,

i would write

A1.formulaArray =
"=-IF(UPPER($EC$14)="Y",AH22*AI22,AN22)*R22*SUMIF(PAY MENT_INDEX,"="
&F22,RNG_OPT_PREM)/COUNTIF(PAYMENT_INDEX,&F22)"

But VBA recognizes the starting inverted commas of the "Y" as the end
of formula and gives me syntax error.
How do i overcome this?

Thanks

Your code looks fine to me, but I believe the problem you are running into happens constantly in scripting. It's the tics. You should alternate the ' and the " signs. That is how the code code is compiled. The " sign signals the end of a line of code so to avoid this alternate with a set of tics which look like this ' . I apologize for having a problem expressing this but I am reasonably sure this is the situation.

Robert Carmon