Modifying a set of existing validation formulas

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:

Please Login or Register  to view this content.

You could use a code like this:

Please Login or Register  to view this content.

Regards,
Antonio

Thanks much, Antonio. This will streamline things considerably!

When executing, I receive the run-time error 91: "Object variable or With block variable not set." I have declared r As Long, but am I missing a set statement somewhere....or is this referring to something else I've missed?

Matt

Probably it is necessary to use:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

If you can please attach a little example of your file.

Regards,
Antonio

Thanks Antonio. I tried adding the suggested code with no success. As requested, I've attached a test file with the code resident in Module 1. When executed it results in the same error code I'm receiving: run-time error 91: "Object variable or With block variable not set."

Thanks again for your assistance.

I correct the code, now seems working fine.

This is the code:

Please Login or Register  to view this content.

Regards,
Antonio

Thanks, Antonio. I think I may have made the attached spreadsheet example too simple. As you can see from the original code above, the validation formula needs to be dynamic rather than static...meaning the drop down list it displays must change based on the value in the cell two cells above it. Hence my use of the =CHOOSE(MATCH... function in the validation formula. In the spreadsheet example I attached previously, it means for example that the contents of cell F20 can change between TypeOne, TypeTwo or TypeThree depending on user input, and the formula for the drop down list in cell F22 must account for this variability. This is so for every 32nd cell beginning with F22 through F1270.

Please Login or Register  to view this content.

In this example, assume the TypeOne and TypeTwo range names have already been created and are in use in the existing validation formula. The purpose of my code is to go into the validation formula and add the range name TypeThree so that its contents will also be made available in the drop down list in F22 when TypeThree appears in cell F20, such that when the contents of cell F20 change, the contents of the drop down list selection in F22 change. This presumes that the range name TypeThree has already been created in the spreadsheet so it is a valid range.

Again, the validation formulas for each of the 40 cells between F22 and F1270 are identical except for the cell address being referenced by the MATCH function (cell F20 for cell F22, cell F52 for cell F54, etc.).

I still receive the run-time error 91: "Object variable or With block variable not set."

Let me know if this further explanation helps.

Matt