title: VBA, how to insert a dynamic / relative cell reference into a .formulaArray method?
body: <p>I have the following code: </p>
  
  <pre><code>  With Sh.Range("A1:A" &amp; LastRow + 1).Offset(0, 4)
      .FormulaArray = "=MAX(IF(A2:A" &amp; LastRow + 1 &amp; "= ** , D2:D" &amp; LastRow + 1 &amp; "))"
      .Value = .Value
    End With
  </code></pre>
  
  <p>In the place where I have **, I would want a dynamic cell reference. If I was using .formulaR1C1, I would have inserted RC[-1], but I can't use that with a .formulaArray. </p>
  
  <p>Does anyone know how I can insert a relative cell reference that would change as the formula being pasted within the range?</p>
  
  <p>Thank you</p>
  
  <p>EDIT # 1</p>
  
  <p>The whole code looks like this: </p>
  
  <pre><code>Sub RemoveDuplicates_SumMarketValue()
  Dim Sh As Worksheet
  Dim LastRow As Long
  Dim Rng As Range
  
  
  
  Set Sh = Worksheets(1)
  
  Sh.Columns(6).Insert
  
  LastRow = Sh.Range("A65536").End(xlUp).Row
  
  With Sh.Range("A1:A" &amp; LastRow).Offset(0, 5)
      .FormulaR1C1 = "=IF(COUNTIF(R1C[-5]:RC[-5],RC[-5])&gt;1,"""",SUMIF(R1C[-5]:R[" &amp; LastRow &amp; "]C[-5],RC[-5],R1C[-1]:R[" &amp; LastRow &amp; "]C[-1]))"
      .Value = .Value
  End With
  Sh.Columns(5).Delete
  Sh.Rows(1).Insert
  
  
  
  Sh.Columns(5).Insert
  
  With Sh.Range("A1:A" &amp; LastRow + 1).Offset(0, 4)
      .FormulaArray = "=MAX(IF(A2:A" &amp; LastRow + 1 &amp; "= A1 , D2:D" &amp; LastRow + 1 &amp; "))"
      .Value = .Value
  End With
  
  
  
  Set Rng = Sh.Range("E1:E" &amp; LastRow + 1)
  
  With Rng
      .AutoFilter Field:=1, Criteria1:="="
      .SpecialCells(xlCellTypeVisible).EntireRow.Delete
  
  End With
  
  End Sub
  </code></pre>
  
  <p>This purpose of this code, is too look though a sample of data and</p>
  
  <ol>
  <li>find duplicates</li>
  <li>sum up values in 5th column associated with duplicates</li>
  <li>remove duplicate rows (except the one that carries the sum from 5th column)</li>
  </ol>
  
  <p>Now I also want it to have the max value from column 4th of all the duplicates to be retained in the final version, but I can't get the array formula to reference the row correctly.</p>