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

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

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