Usando o Excel VBA para encontrar a última linha correspondente

Question

Eu tenho uma planilha que inclui uma data e uma coluna ActionCode. Eu estou usando o evento Worksheet_Change para executar algum código depois que o usuário insere um ActionCode. Eu gostaria de ter um procedimento que (quando o usuário digita um código específico) encontrará a instância anterior mais recente desse código na mesma coluna e, em seguida, retornará a data dessa coluna. Por exemplo:

Date     |ActionCode
11/4/1999| 2
12/5/1999| 3
1/2/2000 | 2
2/3/2001 | 5
3/1/2001 | 2

Quando o usuário inserir o ActionCode "2" em 1/3/2001, eu quero que o código retorne 1/2/2000 em vez de 11/4/1999 . Eu olhei para MATCH , mas parece que só retornaria 11/4/1999 . Eu encontrei várias sugestões para fazer isso inserindo fórmulas em uma planilha, mas não consigo descobrir como adaptá-las ao VBA.

    

Answer 1

Solução VBA:

Aqui está uma solução do VBA usando o evento Worksheet_Change conforme sugerido. Isso lerá a entrada do usuário de F1 e colocará a saída em F2. Presume-se que os dados estejam nas colunas A e B. Para alterar esses locais, você precisará substituir cada instância de cada um com seus endereços de entrada, saída e dados desejados.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim userInput As Variant, codeTable() As Variant, maxDate As Long
Application.EnableEvents = False
'Make sure worksheet change affected user input cell
If Not Intersect(Range("F1"), Target) Is Nothing Then
    userInput = Range("F1").Value
    If userInput = "" Then
        Range("F2") = ""
    Else
        'Store table data in an array for fast processing
        codeTable = Range("A2", Range("B1").End(xlDown)).Value
        maxDate = 0
        'Find max date for input code
        For i = LBound(codeTable, 1) To UBound(codeTable, 1)
            If codeTable(i, 2) = userInput Then
                maxDate = Application.WorksheetFunction.Max(maxDate, codeTable(i, 1))
            End If
        Next i
        'Print output to sheet
        If maxDate = 0 Then
            Range("F2") = "No records"
        Else
            Range("F2") = Format(maxDate, "m/d/yyyy")
        End If
    End If
End If
Application.EnableEvents = True
End Sub

Solução de fórmula:

Você pode obter o mesmo resultado com uma fórmula de matriz - não é necessário adicionar o VBA à sua pasta de trabalho.

Na sua célula de saída desejada, cole o seguinte na barra de fórmulas e depois pressione Ctrl + Deslocar + Enter .

=MAX(($B$2:$B$21=F1)*A2:A21)

Nesta fórmula, B2:B21 mantém seus códigos de ação e A2:A21 são as datas associadas.

Exemplo de saída (ambas as soluções):

    

Answer 2

Você pode usar uma fórmula:

=AGGREGATE(14,6,(Dates<J2)*(ActionCode=J3)*Dates,1)

• J2 = a Data inserida pelo usuário ou TODAY() se você literalmente significa isso.
• J3 = o código de ação inserido pelo usuário

Como estamos usando a função LARGE contra Datas que correspondem à condição de ser menor que a entrada do usuário e também temos um Código de ação = a entrada do usuário, a ordem de entrada de data é irrelevante.

Se as datas forem classificadas como ascendentes, você poderá usar a fórmula mais simples:

=LOOKUP(2,1/((Dates<J2)*(ActionCode=J3)),Dates)