Select first choice in a validation list in a range that are not the same

[Stjernemix]

I have a range of validation lists that needs to be reset to their first value (its a text string not numerical value).

Imagine if the validation lists in the range can be set to any number from 0-20 then you could reset it by the following simple macro:
Sub X()
Range("G16:G22").Value = "0"
End Sub

But in a range of validation lists where the first choice is not the same the above simple macro does not work, also it is text not numbers.
Also some cells in the range does not contain a validation list and is just a normal cell with text.

How can I make a macro that resets the range to the first selection in all the validation lists in the range, while I still have some none validation cells ?

 

[Norie]

Try this, it checks the entire sheet for data validation but you can easily change that.

Sub ResetValidation()
Dim rngValidation As Range
Dim cell As Range
Dim arrValues As Variant

    For Each cell In Cells.SpecialCells(xlCellTypeAllValidation)
        If Left(cell.Validation.Formula1, 1) = "=" Then
            arrValues = Evaluate(cell.Validation.Formula1)
            cell.Value = arrValues(1, 1)
        Else

            arrValues = Split(cell.Validation.Formula1, ",")
            cell.Value = arrValues(0)
        End If

    Next cell

End Sub

 

[Stjernemix]

I tried to change the code from the best of my understanding. But the Array says type mismatch and I couldn't figure out what to change, I tried the numbers.
Also I have no VBA experience, only very basic C a very long time ago.
So if anyone could push me in the correct direction I'd appreciate it.

Thanks

 

[Norie]

How is it not working?

Are you getting errors?

Also, how have you set the data validation up?

 

[Stjernemix]

How is it not working?
When I press the macro button it gives this error:

Are you getting errors? See above and last post.

Also, how have you set the data validation up?

Most of the cells are set up with normal Data Validation:

Two of the cells are set up with a data list in another sheet because I need another cell to be vlookup dependable: (had to blackout some sensitive information)

And again some cells are no lists at call just normal cells with text

 

[Norie]

Can you post some sample data rather than images?

 

[Stjernemix]

Can you post some sample data rather than images?

Link to example

I uploaded a sample macro enabled excel file on onedrive link above.

 

[Norie]

When I try the code I posted in the workbook you uploaded it works without problems.

The data validations are all set to their initial values and there are no errors.

 

[Stjernemix]

When I use your posted code on the test sheet it does this:

As you can see there is some inherent error in the 2 first table cells. Where is shows the entire Data Validation string. (I'm using Excel 365)

Also, the code I posted was me trying to re-code your code so it would only effect a Range in one Column eg. F16 to F22 ("F16:F22").
This is were I get the yellow error in the VBA code, because I tried to re-code it within a Range.

-------------------------
Ok while replying I figured out what I need to change for it to work correctly in the test sheet. I needed to change
arrValues = Split(cell.Validation.Formula1, ",")
to
arrValues = Split(cell.Validation.Formula1, ";")

Now it works in the test sheet, but just without within a Range (since you mentioned your code resets the entire sheet)
-------------------------
But in the real sheet it gives me this error when I run the same code that worked in the test sheet.

 

[Norie]

What happens if you don't change the comma in the Split?