Using " in formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks Mike,

That's what I needed to know.

Richard

Mike H said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top