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.
ActiveCell.Formula2R1C1 = Replace(ActiveCell.Formula2R1C1, "120158", "120159")
maybe?