Obtém o índice do valor selecionado no Excel suspenso

Question

Eu tenho uma lista suspensa gerada no Excel a partir de valores de linha. Eu quero selecionar a partir desse menu suspenso, obter o índice desse valor na matriz suspensa e, em seguida, usar isso para um valor de deslocamento para selecionar o valor de uma coluna diferente.

Não consigo encontrar uma maneira simples de obter o índice do valor selecionado no menu suspenso.

Cada resposta que encontro faz suposições sobre onde a matriz é gerada, mas aqui elas são geradas por diferentes conjuntos de dados que podem ser alterados a qualquer momento.

    

Answer 1

você não precisa do VBA para resolver isso.

1. Defina um intervalo com nome na pasta de trabalho para a lista que você está usando para a validação (no meu caso, tenho <5 , >5 e >10 nas células A2 , A3 & A4 para anos de serviço, portanto, o intervalo nomeado é chamado Years_of_service .

2. A validação está na célula C1 usando o intervalo de nomes definido de Years_of_service para a lista.

3. Fórmula na célula D1 para calcular o deslocamento ou o item selecionado na lista é =MATCH(C1,Years_of_service,0) .

Mark O'Rafferty

    

Answer 2

Atualizado

Tente colar o seguinte VBA em "ThisWorkbook" (se precisar de ajuda com isso, pergunte no comentário):

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

Algumas notas:

• Isso pressupõe que sua caixa suspensa / célula seja chamada de "rngTest". Se não, em seguida, renomeie-o ou altere-o ao longo deste código para o nome existente.
• A linha 15 define a célula exata em que o resultado do índice será gerado. Você precisará alterar os nomes das planilhas e das células para que correspondam aos seus.

Eu testei isso e ele funciona, embora eu não possa ter total crédito, pois o código original era algo que eu encontrei meses atrás. Se você tiver problemas, às vezes é mais fácil replicar esse processo em uma nova planilha até entender a sua teoria de operação.