Determine if cell contains data validation

Question

I am writing a VBA code that goes through a range of cells checking if each cell has data validation (drop down menu) and if not assign one to it from a list on another sheet.

I currently have trouble with the line that checks if the current cell already has data validation. I get error 1004 "no cells were found".

Sub datavalidation()

    Dim nlp As Range
    Dim lrds As Long
    Dim wp As Double
    Dim ddrange As Range

    Sheets("DataSheet").Select

        lrds = ActiveSheet.Range("A1").Offset(ActiveSheet.rows.Count - 1, 0).End(xlUp).Row

        Set nlp = Range("I3:I" & lrds)

        For Each cell In nlp

    'error on following line

            If cell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
                wp = cell.Offset(0, -8).Value

                Set ddrange = ddrangefunc(wp)

            End If

        Next

End Sub

Any ideas? Thank you

Answer 1

I know this question is old, but since it comes up when Googling "excel vba check if cell has validation", I figured I would add my grain of salt.

If the Range object on which you call SpecialCells represents only a single cell, the entire sheet will be scanned to find matches. If you have a very large amount of data, the methods provided in previous answers may become a bit slow.

Hence, here is a more efficient way to check if a single cell has validation:

Function HasValidation(cell As Range) As Boolean
    Dim t: t = Null

    On Error Resume Next
    t = cell.Validation.Type
    On Error GoTo 0

    HasValidation = Not IsNull(t)
End Function