title: Locking a Relative Reference inside a Formula
body: <p>I'm trying to create a search that when found will input a formula into a cell.
  The problem is trying to create a formula that will reference the row that the search is found (<code>OffSet</code>?) and locking it inside the formula (Same as using <kbd>F4</kbd> or <code>$</code> outside of VBA) </p>
  
  <pre><code>Set SrchRng = Range("F6:F30")
  
  For Each cel In SrchRng
  
      'SL Events
         If cel.Value = "SL" Then
  
  
         I = "=RC[-9]-R6C4"
         ''''I want it to be along these lines
         ''''I = "=RC[-9]-$R$C[-9]"                      or
         ''''I = "=RC[-9]-($Cel.Offset(, -9).Value)"
  
  
                cel.Offset(0, 7).Value = I
  
          End If
  </code></pre>
  
  <p>The second cell needs to be locked because I'm using auto fill on a different part of the macro.</p>
  
  <p>Can this be done?</p>