Hello everyone,

I've been stuck on a Data validation list issue for 2 days and I don't manage to solve it..

I have two drop-downs lists containing series of letters, the second depending on the first one :

2jcj7rr.png

First of all, my macro aims to :

1) Populating the Excel sheet Spedizioni aperte with data, letting the column F (for the first list) and G (second dependent list) blank.
2) Initializing the Table and the Ranged names from the sheet Info
3) Get the data range from the sheet Spedizioni aperte
4) Populate this range in the column F with the first data validation list and G with the second one

Here's my function for points 2) to 4):

Sub creaListaEventi()

    Dim wb As Workbook, ws As Worksheet
    Dim lrow, lcol, i As Long
    Dim Inizio, Fine As String

    Const Rowno = 2
    Const ROffset = 1
    Const Colno = 10
    Const COffset = 0  ' in this case, the first column will always contain data.

    On Error GoTo CreateNames_Error

    Sheets("Info").Select

    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    lcol = Cells(Rowno, Columns.Count).End(xlToLeft).Column
    lrow = Cells(Rows.Count, Colno).End(xlUp).Row
    Inizio = Cells(Rowno, Colno).Address
    Fine = Cells(lrow, lcol).Address
    
    'Create the table
    ws.ListObjects.Add(xlSrcRange, Range(Inizio & ":" & Fine), , xlYes).Name = "Table_eventi"
    
    Sheets("Spedizioni aperte").Select
    
    'Select the first cell in the G column to initialize the second drop-down list.
    Range("G" & initEventi).Select
    
    'Create the first drop-down list with the headers of the table
    wb.Names.Add Name:="Eventi", RefersToR1C1:="=Table_eventi[#Headers]"
    '
    wb.Names.Add Name:="col_num", RefersToR1C1:="=MATCH('Spedizioni aperte'!RC[-1],Eventi,0)"
    wb.Names.Add Name:="col", RefersToR1C1:="=INDEX(Table_eventi,,col_num)"
    
    'Create the Named range for the second drop-down list
    wb.Names.Add Name:="sottoEventi", RefersToR1C1:="=IF(OR('Spedizioni aperte'!RC[-1]=""Selezioni..."",'Spedizioni aperte'!RC[-1]=""""),"""",INDEX(Table_eventi,1,col_num):INDEX(Table_eventi,COUNTA(col),col_num))"
    
    'Create the first data validation list in each cell of column F
    With Range("F" & initEventi & ":F" & fineEventi)
    
        .Validation.Delete
        .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Eventi"
        .Validation.ErrorMessage = "Evento inesistente. Si prega di scegliere l'evento nella lista."
    
    End With
    
    'Create the second data validation list in each cell of column G
    With Range("G" & initEventi & ":G" & fineEventi)
        
            .Validation.Delete
            .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=sottoEventi"
            .Validation.ErrorMessage = "Sotto evento inesistente. Si prega di scegliere l'evento nella lista."
        
    End With
    
    On Error GoTo 0
        MsgBox "All dynamic Named ranges have been created"
    Exit Sub

    Exit Sub

CreateNames_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
    ") in procedure CreateNames of Module Technology4U"

End Sub

When I launch the macro, the table and Named ranges are created and the column F is populated correctly but I get an "Error 1004" while it tries to insert the data validation list on the column G.

Senza nome2.png

Does someone have an idea?

I need a fresh eye
Thank you very much for your help !

Fabien.