Macro to filter data and then insert formula onto visible cells

Question

I am trying to filter a worksheet (via Column DL). I then need to insert a formula into Column DQ but only for the visible cells. I was previously using the code below which works except I do not want the specific reference to cell DQ3. This cell can change and thus the wrong formula is copied and pasted.

Dim LastRow As Long
Dim FinalRow As Long

LastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("DL2:DL" & LastRow).AutoFilter Field:=116, Criteria1:= _
    "ABC"

Range("DQ3").Copy

FinalRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("DQ3:DQ" & FinalRow).Select
ActiveSheet.Paste

I tried the code below but it returns a "Run-time error '1004': Application-defined or object-defined error on the final line of code:

Dim LastRow As Long
Dim FinalRow As Long

LastRow = Range("B" & Rows.Count).End(xlUp).Row

Range("DL2:DL" & LastRow).AutoFilter Field:=116, Criteria1:= _
    "ABC"

FinalRow = Range("B" & Rows.Count).End(xlUp).Row
Range("DQ2:DQ" & FinalRow).FormulaR1C1 = "=(RC[-1]-RC[-2])"

I also tried this code:

 Dim LastRow As Long
 Dim FinalRow As Long


LastRow = Range("B" & Rows.Count).End(xlUp).Row

Range("DL2:DL" & LastRow).AutoFilter Field:=116, Criteria1:= _
    "ABC"


FinalRow = Range("B" & Rows.Count).End(xlUp).Row
Set RNG = Range("DQ2:DQ" & FinalRow).SpecialCells(xlCellTypeVisible)

RNG = "=(RC[-1]-RC[-2])"

This runs without any errors but does not fill any data into Column DQ.

Any suggestions on how to get rid of the error or how to achieve my original goal? I am not sure what I am trying will even work but this is where I got stuck.

Thanks!

Answer 1

I was able to achieve my goal using the code below. Posting in case someone else has the same issue in the future.

With ActiveSheet.Range("DQ2:DQ" & Cells(Rows.Count,2).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
  .Cells.FormulaR1C1 = "=((RC[-1]-RC[-2])"`
  .Cells.FillDown`
   Worksheets("WorksheetName").Columns(10).Calculate

End With

Cheers!