VBA Validation Throws 1004 Application -Defined Error

Question

Hi I have the following code which is triggered for thousands of cells. It adds a validation list to each cell (the list values are defined in a names range - the names range is in a separate tab).

Sub CreateList(cell As Variant, rng As String)
     If rng <> Empty Then
          With cell.Validation
             .Delete
             .Add Type:=xlValidateList, Formula1:="=" & rng
             .ShowError = False
          End With
       End If
    End Sub

I call the subroutine from another function which loops through cells. Note totalCols & totalRows are set elsewhere but are the bounds of the data range:

    For i = 2 To totalRows
       For J = 1 To totalCols
          CreateList(Worksheets("Data").cells(i, j), GetRange(Worksheets("Data").cells(1, J).Value)
       Next
    Next

GetRange() takes a heading value (stored in row 1 of the "Data" sheet) and returns a range name (stored in a separate worksheet in the same workbook):

Function GetRange(cell As Variant) As String    
       If cell.Value = "Column One Name" Then
          GetRange = "RangeOne"
       ElseIf cell.Value = "Column Two Name" Then
          GetRange = "RangeTwo"
       Else
          GetRange = ""
       End If
End Sub

When I run the code it sometimes fails with an application defined error 1004. The peculiar thing here is at different points for different datasets. The larger the dataset the less columns it formats. With small datasets (i.e. 1/2 rows) it works - as in it loops through all ranges and adds lists accordingly.

SOME MORE INFO: The datasheet is not locked. When debugging, rng has the correct value. This is confirmed when doing an "Add Watch" in debug mode and checking the range values property of the range. The named range is within workbook scope but is contained in a separate worksheet.

It always stops in the same place when running for the first time for same data sets. It gets half way down a column sometimes populating the cells with the right range values and then just stops with error 1004.

When running for the second time immediately after the first fail - it sometimes stops in the very first cell requiring validation and displays the 1004 error. When this happens, it stops in the first cell requiring adding of validation for any data set. It's as if it breaks the worksheet at this point.

Can you add a counter to createlist to see how many cells it successfully applys DV to? and test on a couple of your datasets. — NickSlash
Run 1: Failed - Counter = 65514; Run 2: Passed - Counter = 70553; Run 3: Failed - Counter = 65514; All were run against different datasets — user559142

Tim Williams Tim Williams · Accepted Answer · 2013-03-22T01:30:01

Sub ApplyValidation()

    Dim c As Range

    For Each c In Worksheets("Data").Cells(1, 1).Resize(1, totalCols).Cells
        With c.Offset(1, 0).Resize(totalRows - 1, 1).Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & GetRange(c)
            .ShowError = False
        End With
    Next c

End Sub

Function GetRange(cell As Variant) As String
    If cell.Value = "Column One Name" Then
       GetRange = "RangeOne"
    ElseIf cell.Value = "Column Two Name" Then
       GetRange = "RangeTwo"
    Else
       GetRange = ""
    End If
End Function

VBA Validation Throws 1004 Application -Defined Error

1 Answers