Excel下拉列表值排列组合宏报错，求指导读取下拉列表值

你的代码报错核心原因是：你直接读取了E、F列单元格的当前显示值，但这两列是带数据验证的下拉列表，你需要的是下拉列表中的所有选项值，而非单个单元格的内容。下面是修正后的完整方案：

关键思路 #

1. 通过Excel的Validation对象获取下拉列表的数据源
2. 解析数据源（支持单元格区域引用或直接输入的逗号分隔列表）
3. 生成两列选项的全排列组合
4. 将组合结果设置为D列的下拉列表源

修正后的VBA代码 #

' 辅助函数：提取指定单元格的数据验证下拉列表选项
Function GetValidationList(rng As Range) As Variant
    Dim valSource As String
    Dim sourceRange As Range
    Dim arr As Variant
    Dim tempArr() As Variant
    Dim i As Long
    
    ' 检查目标单元格是否有数据验证列表
    If Not rng.Validation.Type = xlValidateList Then
        GetValidationList = Array()
        Exit Function
    End If
    
    valSource = rng.Validation.Formula1
    
    ' 处理数据源是单元格区域的情况（比如"=$A$1:$A$10"）
    If Left(valSource, 1) = "=" Then
        Set sourceRange = ThisWorkbook.Range(Mid(valSource, 2))
        arr = sourceRange.Value
        ' 把二维单元格数组转成一维数组
        ReDim tempArr(1 To UBound(arr, 1))
        For i = 1 To UBound(arr, 1)
            tempArr(i) = arr(i, 1)
        Next i
        GetValidationList = tempArr
    Else
        ' 处理数据源是直接输入的逗号分隔列表（比如"a,b,c"）
        GetValidationList = Split(valSource, ",")
    End If
End Function

Sub GenerateCombinationDropdown()
    Dim arr1 As Variant
    Dim arr2 As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim comboArr As Variant
    Dim comboStr As String
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 获取E列和F列下拉列表的所有选项（取整列第一个单元格验证规则即可）
    arr1 = GetValidationList(ws.Range("E1"))
    arr2 = GetValidationList(ws.Range("F1"))
    
    ' 检查是否成功获取到选项
    If UBound(arr1) = 0 Or UBound(arr2) = 0 Then
        MsgBox "E列或F列没有设置有效的下拉列表！"
        Exit Sub
    End If
    
    ' 初始化组合数组（10*10=100个组合）
    ReDim comboArr(1 To 100)
    k = 1
    
    ' 生成全排列组合
    For i = LBound(arr1) To UBound(arr1)
        For j = LBound(arr2) To UBound(arr2)
            comboArr(k) = arr1(i) & ", " & arr2(j)
            k = k + 1
            If k > 100 Then Exit For
        Next j
        If k > 100 Then Exit For
    Next i
    
    ' 将组合数组转为逗号分隔字符串，作为D列下拉列表源
    comboStr = Join(comboArr, ",")
    
    ' 设置D列的数据验证（覆盖D2到D101区域）
    With ws.Range("D2:D101").Validation
        .Delete ' 清除原有验证规则
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=comboStr
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
    
    ' 设置D列标题
    ws.Range("D1").Value = "Result"
End Sub

代码说明 #

• GetValidationList函数：自动识别下拉列表的数据源类型，无论是单元格区域引用还是直接输入的列表，都能返回包含所有选项的一维数组。
• 主过程先获取两列下拉选项，生成100种排列组合后，直接将组合结果设置为D列的下拉列表源，完全匹配你的需求。
• 如果E/F列的下拉列表是基于A/B列的单元格区域，函数会自动读取对应区域的所有值，无需额外修改。

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