| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using " in formula
Hello All,
I have a procedure to initialise each new sheet to a standard. Part of the initialisation is to put a formula in a cell. Unfortunately this formula contains ". (See below) activecell.FormulaR1C1 = "=OFFSET(Codes!E2,MATCH(MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255),Codes!E:E)-2,-1)" So the question is: How do I get the VBA to put this formula in the cell? Thanks Richard |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using " in formula
By far the simplest is to record yourself entering the formula and you will
find the quotes double up. In this example the active cell was A2:- ActiveCell.FormulaR1C1 = _ "=OFFSET(codes!RC[4],MATCH(MID(CELL(""filename"",R[-1]C),FIND(""]"",CELL(""filename"",R[-1]C))+1,255),codes!C[4])-2,-1)" Mike "Richard M Burton" wrote: Hello All, I have a procedure to initialise each new sheet to a standard. Part of the initialisation is to put a formula in a cell. Unfortunately this formula contains ". (See below) activecell.FormulaR1C1 = "=OFFSET(Codes!E2,MATCH(MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255),Codes!E:E)-2,-1)" So the question is: How do I get the VBA to put this formula in the cell? Thanks Richard |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using " in formula
Thanks Mike,
That's what I needed to know. Richard "Mike H" wrote: By far the simplest is to record yourself entering the formula and you will find the quotes double up. In this example the active cell was A2:- ActiveCell.FormulaR1C1 = _ "=OFFSET(codes!RC[4],MATCH(MID(CELL(""filename"",R[-1]C),FIND(""]"",CELL(""filename"",R[-1]C))+1,255),codes!C[4])-2,-1)" Mike "Richard M Burton" wrote: Hello All, I have a procedure to initialise each new sheet to a standard. Part of the initialisation is to put a formula in a cell. Unfortunately this formula contains ". (See below) activecell.FormulaR1C1 = "=OFFSET(Codes!E2,MATCH(MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255),Codes!E:E)-2,-1)" So the question is: How do I get the VBA to put this formula in the cell? Thanks Richard |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use "offset" function in "array formula"? | Excel Discussion (Misc queries) | |||
| =IF(B1,"",SUMPRODUCT(--(B$1:B$5=""),--(A1<A$1:A$5))+1), Biff formula | Excel Worksheet Functions | |||
| Can you replace "TRUE" with " " in an exact formula? | Excel Worksheet Functions | |||
| conditional formula to show "open" or "closed" | Excel Worksheet Functions | |||
| "IF"- "THEN" type Formula based on Null value | Excel Worksheet Functions | |||
Hybrid Mode
