Data validation automatically set to first item in list

[dirtychinchilla]

Morning sirs,

I've been having a good look around for this, but am struggling to find a solution that works.

I have various data validation lists, and would like them automatically to be set to their first value when they are set up (uses a macro). Does anyone have any idea how this might be done?

Thanks in advance,

dirtychinchilla

 

[dirtychinchilla]

It's just a product name, and a range is named after it.

 

[Andrew Poulsom]

That doesn't answer my question. In Name Manager select the name AM_300. What is in the Refers to box at the bottom?

 

[dirtychinchilla]

Oh sorry! It refers to:

Options!$G$150:$G$152

Options being one of the sheets. This list contains values "-, L, P"

 

[Andrew Poulsom]

My code works for me with that scenario. Do you want to put your workbook on a share like Box.com and post the URL so that I can take a look?

 

[dirtychinchilla]

https://app.box.com/s/dy7ydphl0dq7c7imwqll

 

[Andrew Poulsom]

When the error occurs the cell named UnitTypeTran contains #N/A.

 

[dirtychinchilla]

Ah I see! The problem is that that formula resets UnitTypeTran before it changes cell B5, which is the one with the indirect formula in.

Would there be a way to ensure that this problematic bit runs before everything else in that formula?

 

[dirtychinchilla]

OK I solved that problem by putting a blank into the table for the vlookup, so it can never (hopefully) be N/A.

Thank you very much for your help, sir.

 

[dirtychinchilla]

I'm trying to integrate this code into the Update_Selection macro, as I need to set the lower two groups of cells (range ClearDataValidation) and I've made the code this:

Sub Update_Selection()' Update_Selection Macro


    Range("Selections").ClearContents


    With Sheets("Back")
        .Range("Units").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("Criteria"), _
        CopyToRange:=.Range("Extract"), Unique:=True
        .Range("F_UnitName").Copy
    End With
    
    Sheets("Main").Range("Name").PasteSpecial Paste:=xlPasteValues
    Sheets("Main").Range("Options").Cells.SpecialCells(xlCellTypeAllValidation).ClearContents
    
    Dim rngDst As Range
    Dim rngSrc As Range
    Dim arr As Variant
    Dim I As Long
    
    Application.ScreenUpdating = False
    
    arr = Array("F", "F_Target", "Filters", "CP", "CP_Target", "Control_Panels", _
                "M", "M_Target", "Mounting", "CM", "CM_Target", "Cooling_Modules", _
                "HS", "HS_Target", "Heating_Surfaces", _
                "S", "S_Target", "Sensors", "BMS", "BMS_Target", "BMS", _
                "CS", "CS_Target", "Condensation", "EM", "EM_Target", "Energy_Meter", _
                "PWO", "PWO_Target", "Panels_WO", "PW", "PW_Target", "Panels_W", _
                "OG", "OG_Target", "Outside_Grilles", "FC", "FC_Target", "Facade_Cover")


    For I = LBound(arr) To UBound(arr) Step 3


        With Sheets("Options")
        
            Set rngSrc = .Range(Replace(Sheets("Main").Range("UnitType").Value, " ", "_") & "_" & arr(I))


            Set rngDst = .Range(arr(I + 1))
            
        End With


        rngSrc.Copy


        rngDst.PasteSpecial xlPasteValues


        With ActiveWorkbook.Names(arr(I + 2))
            .Name = arr(I + 2)
            .RefersToR1C1 = "=Options!" & rngDst.Resize(rngSrc.Rows.Count, 1).Address(, , xlR1C1)
        End With
        
        Application.DisplayAlerts = False


    Next I
    
    Dim Cell As Range
    For Each Cell In Range("DataValidationClear").Value
        With Cell
            If .Validation.Type = xlValidateList Then
                .Value = Range(Replace(.Validation.Formula1, "=", "")).Cells(1, 1)
            End If
        End With
    Next Cell
    
    Range("Description_Target").MergeArea.ClearContents
    Selection.MergeCells = False
    Range("Price_Target_A").Value = "-"
    Range("Unit_Price_Target_A").Value = "-"
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    Application.Goto Sheets("Main").Range("Name")


End Sub

But I get the "object required" error on the "For Each Cell In Range("DataValidationClear").Value" line.

Thanks again for your help.

 

[Andrew Poulsom]

More like:

For Each Cell In Range("DataValidationClear").Cells