I have an Excel formula where I wish to change range row number found in a specific cell.

=SORT(UNIQUE(FILTER(CHECKLIST!K7:K120180,(CHECKLIST!B7:B120180=INVENTORY!H5)*(CHECKLIST!C7:C120180=INVENTORY!I5)*(CHECKLIST!D7:D120180=INVENTORY!J5)*(CHECKLIST!E7:E120180=INVENTORY!K5)*(CHECKLIST!F7:F120180=INVENTORY!L5)*(CHECKLIST!G7:G120180=INVENTORY!M5)*(CHECKLIST!J7:J120180=INVENTORY!N5))))

For example, I want to update 120180 and replace it with the value found in cell J3 which equals 123456

When I use Find and Replace, I have no issues since I’m manually posting the values. When I create a macro, it doesn’t work because the values appear to be static and don’t change based on cell I want to reference.

Sub vdc_DataRefresh_Lookup()
'
' Updates array formula with new Last Row Number in Checklist Table
'
'
    Range("B2").Select
    ActiveCell.Formula2R1C1 = "=UNIQUE(CHECKLIST!R[5]C:R[120156]C)"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "120158"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "120158"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C[-9]"
    Range("J3").Select
    Selection.Copy
    Cells.Replace What:="120158", Replacement:="120159", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Is there a way to highlight a range of formulas and look for a value (based on a cell) and replace it with a new value (based on a different cell? Here’s a simplified version on what I’m trying to accomplish.

Formula in cell A1 =UNIQUE(CHECKLIST!B7:B120180)
Macro or VBA script that will take value in A2 = 123456
and replace/update formula in Cell A1 UNIQUE(CHECKLIST!B7:B123456)

Tried record macro, updating VBA but results are static not dynamic.