title: Find Formulas in cell across multiple sheets and replace with a new formula but referencing a cell for portion of the formula
body: <p>I am trying to create a macro were I can search multiple spreadsheets in my workbook for a specific formula and replace it with a different formula but changing a variable within the formula as well.</p>
  
  <p>So for instance, I want to find formula:</p>
  
  <pre><code>=SUMPRODUCT(ISNUMBER(MATCH(Actions_qry!$B$2:$B$40000,{"**NameofPerson**"},0))*ISNUMBER(MATCH(Actions_qry!$D$2:$D$40000,{"FormType"},0)))
  </code></pre>
  
  <p>And replace it with Formula</p>
  
  <pre><code>=SUMPRODUCT(ISNUMBER(MATCH(Actions_qry!$B$2:$B$40000,{"**NameofPerson**"},0))*ISNUMBER(MATCH(Actions_qry!$D$2:$D$40000,{" FormType"},0)))
  + SUMPRODUCT(ISNUMBER(MATCH(Actions_qry!$B$2:$B$40000,{"**NameofPerson**"},0))*ISNUMBER(MATCH(Actions_qry!$D$2:$D$40000,{" FormType2"},0)))
  </code></pre>
  
  <p>In this formula though,  I need “NameofPerson” to reference cell B4 for the first iteration of the loop and then offset for a predetermined number of rows to reference the next person in the form.  The forms for each person are identical so they are spaced evenly throughout the form.  I have included a screen cap with what the form looks like to give a better reference.</p>
  
  <p><a href="https://i.stack.imgur.com/2WgEb.png" rel="nofollow noreferrer">Screen Cap of form</a></p>
  
  <p>I have seen macros where people have made an array of stuff to find and then an array of stuff to replace but I was not able to adapt it to this.  It is a little more complex than my limited VBA skills.  I was trying to adapt this script by @Miqi180 to do it.  I am not sure I am even close to doing it though.  Like I said my VBA skills are limited, alike stumbling around in the dark.   Any advice or knowledge would be greatly appreciated.  Thanks!</p>
  
  <p><a href="https://stackoverflow.com/questions/50411913/replacing-text-in-same-column-for-all-sheets">Replacing text in same column for all sheets</a></p>
  
  <p>Sub replaceOldWithNew()</p>
  
  <pre><code>Dim sht As Worksheet
  Dim fndList As Variant
  Dim rplcList As Variant
  Dim x As Long
  
  fndList = Array("old1", "old2")
  rplcList = Array("new1", "new2")
  
  'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
      'Loop through each worksheet in ActiveWorkbook
      For Each sht In ActiveWorkbook.Worksheets
          sht.Range("E:E").Replace What:=fndList(x), Replacement:=rplcList(x), _
          LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht
  Next x
  
  End Sub
  </code></pre>