Excel vba stops when the trying to insert formula to a cell

[kukerjoe]

[FONT=&quot]Hi,

[/FONT]
[FONT=&quot]vba stops when trying to insert formula to a cell with string variable that have apostrophe in it[/FONT]
[FONT=&quot]In this formula[/FONT]
[FONT=&quot]ActiveCell.FormulaR1C1 = "=IF(RC7="""","""",IF('[" & TKNAME & "]aaa'!R[-3]C[18]="""","""",'[<wbr style="font-family: Arial, sans-serif; font-size: 12.8px;">" & TKNAME & "]aaa'!R[-3]C[18]))"[/FONT]
[FONT=&quot]when TKNAME does not apostrophe in it t then he formula is inserted to the cell with no problem ( for example TKNAME=ABC)[/FONT]
[FONT=&quot]but if TKNAME has apostrophe in it then vba stops and does not insert the formula ( for example TKNAME=ABC'[/FONT]
[FONT=&quot]Thank you!

* I asked this question in another forum and didn't get helpful answer[/FONT]

 

[MARK858]

* I asked this question in another forum and didn't get helpful answer

Please can you post a link to the thread in the other forum as per the forum rules so we don't duplicate the answers and waste time on solutions that don't work.

 

[kukerjoe]

https://social.msdn.microsoft.com/F...iable-that-have-apostrophe-in-it?forum=isvvba

 

[MARK858]

Can you post what the formula in the cell would be with the ABC' hard coded manually as that as far as I can see would be an illegal formula.

 

[Fluff]

Hi & welcome to the board
how about

If Right(TKNAME, 1) = "'" Then TKNAME = Left(TKNAME, Len(TKNAME) - 1)