Excel VBA - How to use Vlookup with Letters and Numbers to Delete Queues?

Question

Hello! I am going through great difficulty with a problem using Vlookup in Excel VBA. First of all, I am not an expert in VBA but I managed to use Vlookup but I am not having the effect I would like. I will explain below in more detail the situation with images:

1. I have a table that associates letters to numbers in a separate spreadsheet. I did this because I need to quantify letters and then be able to compare them.

2. With this table, I intend to allow the user, in the main spreadsheet, to enter values that will correspond to codes that contain a combination of numbers and letters (Ex: 91V). And then, with a macro, choose a code value (Ex: 89H). Thus, I would like to create a macro that would delete table rows that in this code have numbers larger than the chosen and letters with smaller values. Example of the inserted table:

3. I was trying to gather information and put together a code that allows the user to enter a value (Ex: 91V) uses Vlookup to look for the value of V. But I don’t go much further than that.

Could you help me?

EDIT:

On request, this was the code I had until then. However, it was a work in process that logically is not even close to what I need.

Sub DeletarIndices()

    indice = InputBox("Digite o Valor Desejado", "Teste")

    Set planilhaV = Sheets("IV")
    Dim vValores As String
    sResult = Application.VLookup(indice, planilhaV.Range("A2:B11"), 2)

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim i As Long
    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Not (Range("A" & i).Value > sResult) Then
            Range("A" & i).EntireRow.Delete
        End If
    Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

EDIT 2

My code is now like this:

Option Explicit

Sub DeletarIndices()

    Dim indice As String   ' To hold our user input letter
    Dim indiceValue As Long   ' To hold the numeric value of our user input letter
    Dim rowLetter As String   ' To hold the current row letter
    Dim rowLetterValue As Long   ' To hold the numeric value of the current row letter
    Dim firstRow As Long   ' First row of your data
    Dim lastRow As Long   ' Last row of your data
    Dim currentRow As Long   ' Current row for your loop counter
    Dim sht As Worksheet   ' To hold the worksheet you're working on
    Dim planilhaV As Worksheet   ' To hold your lookup worksheet

    Set sht = ThisWorkbook.Worksheets("Plan1")   ' Use the name of your worksheet
    Set planilhaV = ThisWorkbook.Worksheets("IV")   ' As in your original example
    firstRow = 1
    lastRow = sht.Range("A" & Rows.Count).End(xlUp).Row

    indice = UCase(InputBox("Digite o IC/IV Desejado", "Teste"))   ' Convert user input to upper case and store
    indiceValue = CLng(Application.VLookup(indice, planilhaV.Range("A2:B11"), 2, False))   ' Creating numeric indice value with lookup table

    For currentRow = lastRow To firstRow Step -1
        rowLetter = UCase(Right(sht.Range("A" & currentRow).Value, 1))   ' Get letter from code in column A, converted to upper case
        rowLetterValue = CLng(Application.VLookup(rowLetter, planilhaV.Range("A2:B11"), 2, False))   ' Creating numeric value for current row letter with lookup table
        If rowLetterValue < indiceValue Then   ' Compare the numeric letter values, and if smaller than user input...
            sht.Rows(currentRow).EntireRow.Delete   ' Delete the row
        End If
    Next currentRow

End Sub

Now I would just need a help to adapt this code with an increment. I need to allow the user to enter not just a letter, but a code (Ex: 91T). To finish, I need that when inserting the example "91T", the code excludes from the table all lines that include Minor Letters and Larger Numbers.

Answer 1

Here’s an example of code that does what you want:

' Verifica se um dado dígito é um número. Retorna True se for, False se não for.
Function ehNumero(ByVal digito As String) As Boolean

    a = Asc(digito)
    If a >= 48 And a <= 57 Then
        ehNumero = True
    Else
        enNumero = False
    End If

End Function

' Separa o código em duas partes, uma numérica e uma alfabética
Function separaCodigo(ByVal codigo As String, ByRef numero As Integer, ByRef letras As String) As Boolean
    p = 0
    For i = 1 To Len(codigo)
        digito = Mid(codigo, i, 1)
        If Not ehNumero(digito) Then ' Achou o ponto de quebra quando encontrar o primeiro dígito não numérico
            p = i
            Exit For
        End If
    Next i

    If p = 0 Or p = 1 Then
        numero = 0
        letras = ""
        separaCodigo = False
    Else
        codigo = UCase(codigo)
        numero = Int(Mid(codigo, 1, p - 1))
        letras = Mid(codigo, p)
        separaCodigo = True
    End If
End Function

' Obtém o valor de uma sequencia de letras na tabela de códigos da configuração
Function valorDasLetras(ByVal letras As String) As Integer
    On Error GoTo trataErro

    valorDasLetras = Application.VLookup(letras, Range("códigos"), 2, False) ' O último parâmetro (False) é FUNDAMENTAL!

    ' Você também pode fazer assim:
    'valorDasLetras = Application.VLookup(letras, Worksheets("Configuração").Range("A2:B11"), 2, False)
    ' Mas é muito melhor ter a definição dos limites num lugar só, por isso use uma tabela nomeada como acima

    Exit Function

trataErro:
    valorDasLetras = 0 ' As letras não existem na tabela de configuração

End Function

'Deleta as linhas da tabela que estejam entre o intervalo dado pelo número de limite inferior e pela letra de limite superior
'Retorna a quantidade de linhas excluidas
Function deletar(ByVal numero As Integer, letras As String) As Integer

    ' Pega o valor das letras do código na tabela de configuração
    valor = valorDasLetras(letras)
    If valor = 0 Then ' O código não foi encontrado
        deletar = -1
        Exit Function
    End If

    ' Define os limites inferior e superior para a deleção
    limInf = numero
    limSup = valor

    ' Seleciona as linhas que serão deletadas da planilha de dados, conforme elas se encaixarem nos limites definidos
    Dim dados As Worksheet
    Set dados = Application.Worksheets("Dados")

    Dim linhasPraDeletar As Range ' Vai guardar as linhas a serem deletadas
    totalLinhas = 0

    linha = 1
    Do While True
        ' Pega o código da linha atual
        curCodigo = dados.Cells(linha, 1) ' Eu coloquei na coluna A pra facilitar o teste

        ' Termina se acabaram os dados da planilha
        If curCodigo = "" Then
            Exit Do
        End If

        ' Separa o código da linha atual em número + letras
        Dim curNumero As Integer
        Dim curLetras As String
        If Not separaCodigo(curCodigo, curNumero, curLetras) Then
            deletar = -1
            Exit Function
        End If

        ' Pega o valor das letras do código na tabela de configuração
        curValor = valorDasLetras(curLetras)
        If curValor = 0 Then ' O código não foi encontrado
            deletar = -1
            Exit Function
        End If

        ' Verifica se o número e as letras estão no intervalo desejado
        ' Se estiver, seleciona toda a linha
        If curNumero > limInf And curValor < limSup Then
            If linhasPraDeletar Is Nothing Then
                Set linhasPraDeletar = dados.Rows(linha)
            Else
                Set linhasPraDeletar = Union(linhasPraDeletar, dados.Rows(linha))
            End If
            totalLinhas = totalLinhas + 1
        End If

        linha = linha + 1
    Loop

    if totalLinhas > 0 Then
        'Set salva = ActiveCell ' <=========== (1)

        linhasPraDeletar.Select
        'linhasPraDeletar.EntireRow.Delete ' <===== Descomente essa linha pra fazer a exclusão de fato

        'salva.Select ' <============= Descomente essa linha e a linha (1) lá em cima para guardar e voltar a célula de seleção atual do usuário
    End If

    deletar = totalLinhas

End Function

' Função atribuída ao clique do botão, para teste
Sub BotãoDeletar_Clique()
    'On Error GoTo trataErro

    ' Lê o código do usuário
    msg = "Digite o código de delimitação do filtro no formato 99XX (sendo que 99 é o número do limite inferior e XX é a letra do limite superior):"
    codigo = InputBox(msg, "Código")
    If codigo = "" Then ' Cancelado
        Exit Sub
    End If

    ' Separa o código no número + letras
    Dim numero As Integer
    Dim letras As String
    If Not separaCodigo(codigo, numero, letras) Then
        MsgBox ("Código inválido: " & codigo)
        Exit Sub
    End If

    ' Chama a função de deleção
    linhas = deletar(numero, letras)
    If linhas = -1 Then
        MsgBox ("Há um error com um código (a letra não existe na configuração)")
    Else
        If linhas = 0 Then
            MsgBox ("Não há linhas no intervalo requisitado - nenhuma linha foi deletada")
        Else
            MsgBox ("Foi(ram) deletada(s) " & linhas & " linha(s)")
        End If
    End If

    Exit Sub

trataErro:
    MsgBox ("O código não está no formato esperado.")

End Sub

His explanation is in the commentary, but basically what he does is:

1. Read the code provided by the user.
2. Separates the number and letters that will be used to set the search limits. Also get the value of the sequence of letters in the configuration table (using vlookup exact - for this the last parameter needs to be False).
3. Scans the data table row by row.
4. On each line, check if its code is within limits, doing the same separation process in 2. If it is, save this line to use later (and accumulate a variable used to return the number of lines)
5. At the end, select the lines that will be deleted and delete them.

Two important remarks. The first is that in the code the exclusion is commented (as well as the codes that save and restore the position originally selected from the user) so that the selection performed for deletion stay active for you to debug visually! A second observation is that this code uses a table named for that the range of cells is not fixed in the code (hard-coded). This makes future maintenance much easier:

Result using the code "89H":