title: Excel VBA - Replace a text inside cell's formula
body: <p>I have faced a strange thing in Excel VBA coding.</p>
  
  <p>I have a sheet full of formulas, each refers to the cells in other sheets.</p>
  
  <p>This is a sample formula in cell A1, for example:</p>
  
  <pre><code>=IF('General Inputs &amp; Summary'!B6="","",'General Inputs &amp; Summary'!B6)
  </code></pre>
  
  <p>I want to dynamically change the old tab names in formulas, with new tab names.</p>
  
  <p>When I try this code:</p>
  
  <pre><code>oldStr = "'General Inputs &amp; Summary'"
  newStr = "'test'"  
  Range("A1").Formula = Replace(Range("A1").Formula, oldStr, newStr)
  </code></pre>
  
  <p>it results in:</p>
  
  <pre><code>=IF(test!B6="","",test!B6)
  </code></pre>
  
  <p>instead of:</p>
  
  <pre><code>=IF('test'!B6="","",'test'!B6)
  </code></pre>
  
  <p>You see, single Quotes are automatically removed, so the new formula fails!</p>
  
  <p>Any solutions for this, please?</p>