确定单元格是否包含数据验证 [英] Determine if cell contains data validation

问题描述

我正在写一个VBA代码，通过一系列单元格检查每个单元格是否具有数据验证（下拉菜单），如果没有从另一个表单上的列表中分配一个。

我目前在检查当前单元格是否已经进行了数据验证的行中有问题。我得到错误1004没有单元被发现。

  Sub datavalidation（）
 
 Dim nlp As范围
 Dim lrds As Long 
 Dim wp As Double 
 Dim ddrange As Range 
 
 Sheets（DataSheet）。选择
 
 lrds = ActiveSheet.Range（A1）。Offset（ActiveSheet.rows.Count  -  1，0）.End（xlUp）.Row 
 
设置nlp = Range（I3：I& lrds ）
 
对于每个单元格在nlp 
 
'下面的行
 
如果cell.SpecialCells（xlCellTypeSameValidation）.Cells.Count< 1然后
 wp = cell.Offset（0，-8）.Value 
 
设置ddrange = ddrangefunc（wp）
 
如果
 
下一个
 
 End Sub 
  

任何想法？
谢谢

解决方案

  Dim cell As Range，v As Long 
 
对于每个单元格在Selection.Cells 
v = 0 
在错误恢复下一个
v = cell.SpecialCells（xlCellTypeSameValidation）.Count 
错误GoTo 0 
 
如果v = 0然后
 Debug.Print无验证
 Else 
 Debug.Print有验证
结束如果
下一个
  

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

解决方案

Dim cell As Range, v As Long

For Each cell In Selection.Cells
    v = 0
    On Error Resume Next
    v = cell.SpecialCells(xlCellTypeSameValidation).Count
    On Error GoTo 0

    If v = 0 Then
        Debug.Print "No validation"
    Else
        Debug.Print "Has validation"
    End If
Next

这篇关于确定单元格是否包含数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！