Using " in formula

[Guest]

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),FIND("]",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

 

[Guest]

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

 

[Guest]

Thanks Mike,

That's what I needed to know.

Richard

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

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),FIND("]",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