☰

获取 Excel 下拉列表中选定值的索引

tag-icon • tag-icon microsoft-excel worksheet-function
获取 Excel 下拉列表中选定值的索引

我在 Excel 中根据行值生成了一个下拉列表。我想从该下拉列表中进行选择，获取该值在下拉数组中的索引，然后使用该索引作为偏移值从不同的列中选择值。

我似乎找不到一种简单的方法来获取下拉菜单中选定值的索引。

我发现的每个答案都对数组的生成位置做出了假设，但在这里，它们是由可以随时更改的不同数据集生成的。

答案1

您不需要 VBA 来解决这个问题。

  1. 在工作簿中为用于验证的列表定义一个命名范围（在我的例子中，我有<5，>5并且>10在单元格中A2，A3&A4表示服务年限，因此命名范围称为Years_of_service。

  2. 验证是在单元格中使用列表C1定义的名称范围进行的。Years_of_service

  3. 单元格中D1用于计算偏移量或列表中选定项目的公式是=MATCH(C1,Years_of_service,0)。

马克·奥拉弗蒂

答案2

更新

尝试将以下 VBA 粘贴到“ThisWorkbook”中（如果您需要帮助，请在评论中询问）：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ValidationIndex As Long
Dim rngTest As Excel.Range

'assumes the data validation is in a cell named "rngTest"
On Error Resume Next
Set rngTest = Sh.Range("rngTest")
If rngTest Is Nothing Then
    Exit Sub
End If
On Error GoTo 0

If Not Intersect(ActiveCell, Sh.Range("rngTest")) Is Nothing Then
    ValidationIndex = GetValidationIndex
    Sheets("Sheet1").Range("E2").Value = ValidationIndex
End If
End Sub

Function GetValidationIndex() As Long
'returns a 1-based index
Dim rngTest As Excel.Range
Dim varValidationString As Variant
Dim ErrNumber As Long
Dim i As Long

With ActiveCell.Validation
    If .Type = xlValidateList Then    '3
        On Error Resume Next
        Set rngTest = ActiveCell.Parent.Range(.Formula1)
        'I do this goofy thing with ErrNumber to keep my indenting and flow pretty
        ErrNumber = Err.Number
        On Error GoTo 0
        'if the Validation is defined as a range
        If ErrNumber = 0 Then
            GetValidationIndex = Application.WorksheetFunction.Match(ActiveCell.Value2, rngTest, 0)
            Exit Function
        'if the validation is defined by comma-separated values
        Else
            varValidationString = Split(.Formula1, ",")
            For i = LBound(varValidationString) To UBound(varValidationString)
                If varValidationString(i) = ActiveCell.Value2 Then
                    GetValidationIndex = i + 1
                    Exit Function
                End If
            Next i
        End If
    End If
End With
End Function

几点说明：

  • 假设您的下拉框/单元格名为“rngTest”。如果不是，则重命名它或在整个代码中将其更改为其现有名称。
  • 第 15 行定义了索引结果将输出到的确切单元格。您需要更改工作表和单元格名称以匹配您自己的名称。

我已经测试过了，它确实有效，不过我不能完全归功于我，因为原始代码是我几个月前发现的。如果您遇到问题，有时在新的工作表中复制此过程会更容易，直到您理解它的工作原理。

答案3

函数“GetValidationIndex”很好。但是，对于某些区域设置，行“varValidationString = Split(.Formula1, ",")”无效，因为用于分隔不同值的字符是“;”。

相关内容