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

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
What happens if you don't change the comma in the Split?
It does the same as in the test sheet it will where it will show the entire Data Validation string (first picture in last post).
Also, it only seems to effect the cell G22 for some reason.
Also, another note is that the VLOOKUP data is in another sheet, not in the same sheet like in the test file.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I end up hard coding it like this

Sub Reset_size()
Dim Cell As Range

Set Cell = Cells(16, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(17, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(18, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(19, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(20, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(21, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(22, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(24, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(25, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(29, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(31, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(33, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(39, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(40, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(41, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(42, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(43, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(44, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(45, 6)
Cell.Value = Range(Split(Mid(Cell.Validation.Formula1, 2), ";")(0)).Value

Set Cell = Cells(46, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(47, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(48, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(49, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(51, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(52, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(53, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(54, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(55, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(56, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(57, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(58, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(59, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(60, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(61, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(62, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(64, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(65, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(66, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(71, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(72, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(73, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(77, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(78, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

End Sub


with Reset Data Validation Lists to first item, method range has failed
for reference
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What sort of validation to you have in F45 that you need to use this?
VBA Code:
Set Cell = Cells(45, 6)
Cell.Value = Range(Split(Mid(Cell.Validation.Formula1, 2), ";")(0)).Value
As far as I can see that appears to be the only validation that differs from the others.
 

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
This is the one where the data validation list comes from a table in another sheet (picture in last post) & has a dependable vlookup function in another cell tied to it.
There used to be more of these cells but my data changed.

Also, as you can see I skipped some numbers which are the ones that are just normal cells with text.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you post the formula for that validation?
 

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
vlookup cell
1608113226584.png

data validation cell
1608113241709.png
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
I added a similar data validation and the original code I posted worked.:eek:
 

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
It's probably something else in the excel file that ****s it up. Because your code also worked in the test excel file that I posted.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top