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

kukerjoe

New Member
Joined
Jan 1, 2018
Messages
7
[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]
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
* 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.
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,865
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board
how about
Code:
If Right(TKNAME, 1) = "'" Then TKNAME = Left(TKNAME, Len(TKNAME) - 1)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top