Excel VBA - How to use VLookup with Letters and Numbers to Delete Rows?

0

Hello! I'm experiencing great difficulty with a problem using VLookup in Excel VBA. First of all, I am not VBA expert but I have been able to use VLookup but I am not having the effect I would like. I will explain below with more detail the situation with images:

  • I have a table that associates letters with numbers in a separate worksheet. I did this because I need to quantify letters so I can compare them later.
  • With this table, I want to allow the user, in the main worksheet, to enter values that will correspond to the codes that contain a combination of numbers and letters (Ex: 91V). And then, with a macro, choose a code value (Ex: 89H). So, I would like to create a macro that would delete table rows which in this code have numbers larger than the chosen one and letters with smaller values. Example of the inserted table:
  • I was trying to gather information and I put a code that allows the user to enter a value (Ex: 91V) uses VLookup to look for the value of V. But I do not go much further.
  • 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 nowhere near 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 now looks 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 some help to adapt this code with an increment. I need to allow the user to insert not only a letter, but a code (Ex: 91T). To finish, I need to insert the example "91T", the code excludes from the table all the lines that include Minor Letters and Bigger Numbers.

        
    asked by anonymous 07.02.2017 / 15:59

    1 answer

    3

    Here's an example 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 comments, but basically what he does is:

  • Read the code entered by the user.
  • Separate the number and letters that will be used to set search limits. It also gets the value of the string of letters in the configuration table (using vlookup exact - so the last parameter needs to be False ).
  • Scale the data table row by line.
  • In each row, 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 rows)
  • At the end, it selects the lines to be deleted and deletes them.
  •   

    Two important observations. The first is that in the code the exclusion is   commented out (as well as the codes that save and restore the   originally selected from the user) so that the selection made   for deletion to remain active for you to debug visually! THE   The second note is that this code uses a named table to   that the cell range is not hard-coded. That   greatly facilitates future maintenance:

         

    Resultusingcode"89H" :

        
    08.02.2017 / 17:49