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]

Thank you, got the same object global error though!

 

[Andrew Poulsom]

I tested the code before posting it. In what circumstances does it fail?

 

[dirtychinchilla]

It's having trouble with this bit:

 .Value = Range(Range(Replace(Replace(.Validation.Formula1, "=INDIRECT(", ""), ")", "")).Value).Cells(1, 1).Value

 

[Andrew Poulsom]

And what is Formula1 when you get the error?

 

[dirtychinchilla]

"=INDIRECT(UnitTypeTran)" again

 

[Andrew Poulsom]

Maybe the order in which the Data validation was created is important:

Sub Test()
    Dim Cell As Range
    For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation).Cells
        With Cell
            If .Validation.Type = xlValidateList Then
                If InStr(.Validation.Formula1, "=INDIRECT") = 0 Then
                    .Value = Range(Replace(.Validation.Formula1, "=", "")).Cells(1, 1).Value
                End If
            End If
        End With
    Next Cell
    For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation).Cells
        With Cell
            If .Validation.Type = xlValidateList Then
                If InStr(.Validation.Formula1, "=INDIRECT") > 0 Then
                    .Value = Range(Range(Replace(Replace(.Validation.Formula1, "=INDIRECT(", ""), ")", "")).Value).Cells(1, 1).Value
                End If
            End If
        End With
    Next Cell
End Sub

 

[dirtychinchilla]

That gave the same error, with the value being the same. This data validation is the first one in the list.

 

[Andrew Poulsom]

I can't reproduce the error. What does then name UnitTypeTran refer to and what's in the reference when the error occurs?

 

[dirtychinchilla]

How strange.

UnitTypeTran refers to the unit type, and has the following formula in it:

=VLOOKUP(UnitType,Unit_Code_Turndown,5,FALSE)

This would have something in it like AM_300 at the time the code goes through.

 

[Andrew Poulsom]

What does the name AM_300 refer to (the formula in the Refers To box)?