Macro to reset column to first item in data validation drop down list

[RAS123]

Hi there,

I wonder if I might find help here,

From F9:F325 I have cells with data validation '=IF(L9,DropdownRange,E9)' to '=IF(L325,DropdownRange,E325)', which works fine.

I would like to click a button to reset every cell in F9:F325 to the first (and only) item in the dropdown when the corresponding L#=False.

Any help appreciated,

Many thanks

RAS

 

[Danmc]

can you upload a sample (desensitized) workbook (use box or dropbox if you need)?

 

[gallen]

Hello,


Assuming your variable DropDownRange is indeed a range object:
This code loops through each cell in Column L of your range and if it is False it changes the cell in column F to the first value in your DropDownRange

Sub ResetCells()
    Dim c As Range
    
    For Each c In Range("L9:L325")
        If c = "False" Then
            Range("F" & c.Row) = DropDownRange.Cells(1, 1)
        End If
    Next c
End Sub

 

[RAS123]

Hi, I registered only today so am a complete MrExcel newbie. How can I upload the simplified worksheet i've created to illustrate the problem?

 

[Danmc]

Hi, I registered only today so am a complete MrExcel newbie. How can I upload the simplified worksheet i've created to illustrate the problem?

create account on dropbox.com (or box.com) and share the link in this post.

 

[RAS123]

can you upload a sample (desensitized) workbook (use box or dropbox if you need)?

https://www.dropbox.com/s/w9lahbh1805dt7c/Example Sheet.xlsx?dl=0

I'm hoping the above link works ok? Many thanks

 

[RAS123]

Hello,


Assuming your variable DropDownRange is indeed a range object:
This code loops through each cell in Column L of your range and if it is False it changes the cell in column F to the first value in your DropDownRange

Sub ResetCells()
    Dim c As Range
    
    For Each c In Range("L9:L325")
        If c = "False" Then
            Range("F" & c.Row) = DropDownRange.Cells(1, 1)
        End If
    Next c
End Sub

Hi Gallen, thank you for trying. Perhaps this link makes the problem a bit clearer

https://www.dropbox.com/s/w9lahbh1805dt7c/Example Sheet.xlsx?dl=0

Thanks

 

[sykes]

Hi, and welcome to Mr. Excel!
It's often that case that you want to upload a copy of just part of your worksheet (selected cells/whole table etc), as a picture really is worth a thousand words.

If you have a look here you'll see how to download & install the HTML-maker.
It's a great piece of kit, and is only an Excel add-in, so it's easy to download, install & use. It creates HTML of your selected cells (AND formulae, if you wish) and places the code onto your clipboard - for you to paste straight into your post on the forum.

All the best