Good day. I have a list of 40 non-adjacent cells in a worksheet that contain validation formulas that include the MATCH function. I need to update the formula for each to include an additional MatchType in the formula string. Each formula is identical except for the Lookup_value, which is always a cell reference two cells above the cell containing the validation formula to be updated. I have created the code below via macro recording that will update the validation formula as needed, but its a full replacement and I will have to replicate it 40 times. Is there a method by which I can loop through each cell that has the validation formula and simply modify the existing formula in each without the full scale 'delete and add' approach I've created below?

Many thanks for any assistance or guidance you can provide. Existing code as follows:


Sub ValidationUpdate()

Dim wbThis As Workbook

' Code designed to modify the validation formula that exists in 40 specific cells within the worksheet.
' Modification needed is to simply add a "TypeFive" to the series of match types in the MATCH formula.
' Note that the cell containing the lookup value in each formula is always two cells above
'       the cell that contains the validation list formula.

 
    wbThis.Worksheets("Section Updates").Activate
    
'   Section 1 (the drop-down list displayed in cell F22 is based on the selection made from the drop-down list in F20)
    
    Range("F22").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
       "=CHOOSE(MATCH($F$20,SectionType,0),TypeOne,TypeTwo,TypeThree,TypeFour,TypeFive)"   'TypeOne thru TypeFour already exist; TypeFive needs to be added
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "Please select response from the drop-down list provided"
        .ShowInput = True
        .ShowError = True
    End With
                       
'   Section 2
    
    Range("F54").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
       "=CHOOSE(MATCH($F$52,SectionType,0),TypeOne,TypeTwo,TypeThree,TypeFour,TypeFive)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "Please select response from the drop-down list provided"
        .ShowInput = True
        .ShowError = True
    End With

'   Sections 3 thru 40 must also be updated, with the cell range to be selected for updating as follows
'   All 40 cells containing a validation formula that needs to be updated:
'   [F22,F54,F86,F118,F150,F182,F214,F246,F278,F310,F342,F374,F406,F438,F470,F502,F534,F566,F598,F630,F662,F694,
'    F726,F758,F790,F822,F854,F886,F918,F950,F982,F1014,F1046,F1078,F1110,F1142,F1174,F1206,F1238,F1270]


End Sub