title: How can I add a single quote to a VBA string used as a search and replace string in a formula?
body: <p>How can I add a single quote to a VBA string used as a search and replace string in a formula?  I have the following code:</p>
  
  <pre><code>Dim sht1 As Worksheet
  Dim fnd1 As Variant
  Dim rplc1 As Variant
  Dim fnd2 As Variant
  Dim rplc2 As Variant
  
  fnd1 = "Instructions!"
  rplc1 = "Info!"
  fnd2 = "#REF!$A$7:$F$2000"
  rplc2 = "INDIRECT(CONCATENATE(" '","Info!G2","'","!a:f"),1)"- this is the issue line
  
  For Each sht1 In ActiveWorkbook.Worksheets
  sht1.Cells.replace What:=fnd1, Replacement:=rplc1, _
  LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
  SearchFormat:=False, ReplaceFormat:=False
  
  sht1.Cells.replace What:=fnd2, Replacement:=rplc2, _
  LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
  SearchFormat:=False, ReplaceFormat:=False
  
  Next sht1
  </code></pre>
  
  <p>`</p>
  
  <p>The rplc2 replacement string will not work because I cannot get the single quote needed to reference another worksheet to be part of the replacement string.  I can not figure out how to add the single quote needed for a sheet reference in the concatenate formula.  I tried:</p>
  
  <pre><code>rplc2 = "INDIRECT(CONCATENATE(char(34),"Info!G2",char(34),"!a:f"),1)"
  </code></pre>
  
  <p>But this doesn't work either.  I also tried several other methods but I can get a combination that works. </p>