Получить индекс выбранного значения в Excel из выпадающего списка

Question

У меня есть выпадающий список, созданный в Excel из значений строк. Я хочу выбрать из этого раскрывающегося списка, получить индекс этого значения в раскрывающемся массиве, а затем использовать его для значения смещения, чтобы выбрать значение из другого столбца.

Я не могу найти простой способ получить индекс выбранного значения в раскрывающемся списке.

Каждый ответ, который я нахожу, делает предположения о том, где генерируется массив, но здесь они генерируются различными наборами данных, которые могут быть изменены в любое время.

Answer 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) .

Марк О'Рефферти

Answer 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 определяет точную ячейку, в которую будет выведен результат индекса. Вам нужно изменить названия листа и ячейки, чтобы они соответствовали вашим собственным.

Я проверил это, и оно работает, хотя я не могу взять полную оценку, поскольку оригинальный код был чем-то, что я нашел несколько месяцев назад. Если у вас возникли проблемы, иногда проще воспроизвести этот процесс в новом рабочем листе, пока вы не поймете его теорию работы.