Find/Replace Row number within Formula

Question

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.

Answer 1

Instead of using VBA, if you're simply trying to have the unique function take in a set of cells, you can use indirect and an address formula to populate your unique formula.

Combine two Address formulas to generate the entire range you need in the unique function like so:

=Address( row( [First Cell]), Column( [first cell]) ) &":"& address( counta( [column of list]), column( [first cell]) )

Then reference this cell in the unique function (in my example B1)

=Unique( indirect(B1) )