Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting formulae
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting formulae
Within a string, you must double the double-quotes:
A1.formulaArray = "=-IF(UPPER($EC$14)=""Y"",AH22*AI22,AN22)*R22*SUMIF(P AYMENT_INDEX,""=""&F22,RNG_OPT_PREM)/COUNTIF(PAYMENT_INDEX,&F22)" HTH -- AP a écrit dans le message de news: ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting formulae
Just as in XL, double the quotes:
A1.formulaArray = "=-IF(UPPER($EC$14)=""Y"",AH22*AI22,..." In article .com, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the results of formulae between different spreadsheets. | Excel Worksheet Functions | |||
Change to formulae | Excel Worksheet Functions | |||
Maintaining cell references when inserting cells elsewhere | Excel Discussion (Misc queries) | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
XL2002 Clipboard doesn't paste formulae | Excel Discussion (Misc queries) |