VBA下拉列表IFS公式运行时错误'1004'排查请求

Let's walk through exactly why your code is hitting that 1004 error, and how to fix it to get your dynamic dropdown working as intended.

Key Issues Causing the Error #

1. Useless (and Broken) Assignment Line #

First, this line is completely invalid and serves no purpose:

JT = Columns("E:E").Select

The Select method returns an object, not a string, so you can't assign it to a String variable like JT. Even if this didn't throw an error, it's unnecessary—you should always reference ranges directly instead of relying on Selection (it's fragile and prone to bugs if the user clicks elsewhere while the code runs).

2. Invalid Formula1 for Data Validation #

The main culprit is your Formula1 argument. Data validation lists require a valid source of options:

• A cell range (e.g., =A1:A5)
• A comma-separated string of values (e.g., "Red,Blue,Green")
• A named range pointing to a list of values

Your IFS function only returns a single value (like B, Co, or E) based on L2's value—not a list of options. Excel can't interpret a single value as a dropdown source, hence the 1004 error.

Fixed Code Solutions #

We need to rewrite the logic to dynamically set the dropdown source based on L2's value. Here are two reliable approaches:

Option 1: Use Named Ranges (Best for Scalable Lists) #

First, create named ranges for each set of dropdown options (e.g., name the range for M2's case List_B, N2's case List_Co, etc.). Then use a Select Case statement to pick the right named range:

Dim targetRange As Range
Set targetRange = Columns("E:E") ' Directly reference the target column

With targetRange.Validation
    .Delete ' Clear existing validation first
    
    ' Dynamically set dropdown source based on L2's value
    Select Case Range("L2").Value
        Case Range("M2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_B"
        Case Range("N2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_Co"
        Case Range("O2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_E"
        Case Range("P2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_En"
        Case Range("Q2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_I_T"
        Case Range("R2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_O"
        Case Range("S2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_P_B"
        Case Range("T2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_Sp"
        Case Range("U2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=List_SII"
        Case Else
            ' Fallback if no match is found
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="No valid options available"
    End Select
    
    ' Keep your existing validation settings
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Option 2: Hardcode Comma-Separated Values (For Small, Fixed Lists) #

If your dropdown options are short and won't change often, you can directly pass comma-separated strings instead of named ranges:

Dim targetRange As Range
Set targetRange = Columns("E:E")

With targetRange.Validation
    .Delete
    
    Select Case Range("L2").Value
        Case Range("M2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="Option1,Option2,Option3" ' Replace with your B list
        Case Range("N2").Value
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="OptionA,OptionB,OptionC" ' Replace with your Co list
        ' Add the rest of your cases with their respective value strings
        Case Else
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="No valid options"
    End Select
    
    ' Keep your validation settings
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Extra Tips #

• Remove the Operator:=xlBetween parameter—it's only used for numeric range validation, not list validation, and doesn't serve any purpose here.
• Double-check that L2 and the values in M2:U2 match exactly (no extra spaces, consistent capitalization) to ensure the Select Case works correctly.
• Avoid using Selection in VBA whenever possible—direct range references are far more reliable.

内容的提问来源于stack exchange，提问作者TheSpycyMeatbol