Guru do Excel e Power BI

Enviado: **14 Jul 2019 às 19:46**

Bom Dia Senhores.

Tento fazer uma validação de dados em que:

Se a combinação de D&E&F for encontrada em qualquer linha, então G da linha da combinação D&E&F = "conciliado"

Eu consegui fazer com que essa validação funcione desde que D&E&F for igual a a A&B&C, e que essa combinação seja encontrada na mesma linha, porém preciso que seja validado essa combinação quando A&B&C for igual a D&E&F em linhas diferentes conforme demonstro em meu modelo anexo.

No meu modelo a célula G2 e G5 deve apresentar a string "conciliado", pois a combinação de D&E&F da linha 2 está na linha 4.

Não sei se estou certo ou próximo disso, mas acredito que a solução seria misturar um VlookUP com IF e percorrer linhas, mas isso eu ainda não consigo fazer.

Gostaria da ajuda dos senhores ou pelo menos um caminho para eu conseguir fazer.

Essa validação é diferente de um outro tópico que postei em que está aberto ainda, contudo são similares.

Enviado: **15 Jul 2019 às 07:56**

Fiz por fórmula. Veja se ajuda.

Código: Selecionar todos

=SE(ÉNÚM(ÍNDICE(CORRESP(D2&E2&F2;$A$2:$A$8&$B$2:$B$8&$C$2:$C$8;0);));"Conciliado";"")

Enviado: **15 Jul 2019 às 12:55**

Olá BabDallas.

Primeiramente muito obrigado pela disponibilidade para me ajudar.

Sua função ficou perfeita. Trata-se de uma função de "gente grande".

Estudarei bastante esta função para melhor entender.

Você me ajudou muito, mas se possível e se não for pedir demais você ou algum colega poderia transformar isso em VBA? Confesso que neste momento não sei por onde começar a aplicar isso em VBA, pois iniciei meus estudos em poucos meses.

Em VBA, pois meu arquivo que será aplicado é gerado pelo sistema com milhares de linhas, então em VBA seria ideal.

Mesmo que não seja possível muito grato pela ajuda.

Enviado: **15 Jul 2019 às 16:39**

Deixei as 2 soluções, fórmula e VBA.

Código: Selecionar todos

Option Explicit

Public Sub ValidarDados()
    Dim lngUltLin           As Long                                 '?ltima linha preenchida
    Dim lngContLin          As Long                                 'Contador de linhas
    Dim strProcura          As String                               'String com informa??es concatenadas das colunas D, E e F
    Dim vrtConcatenado      As Variant                              'Matriz com os dados para procurar
    
    With wshConcilia
        lngUltLin = .Cells(.Rows.Count, 1).End(xlUp).Row            '?ltima linha preenchida na coluna A
        
        ReDim vrtConcatenado(1 To lngUltLin - 1, 1 To 1) As Variant
        
        'Limpa coluna H
        .Range("H2:H" & lngUltLin).ClearContents
        
        'Faz loop para a matriz de dados
        For lngContLin = 1 To lngUltLin - 1
            vrtConcatenado(lngContLin, 1) = .Cells(lngContLin + 1, 1).Value2 & _
                                            .Cells(lngContLin + 1, 2).Value2 & _
                                            .Cells(lngContLin + 1, 3).Value2
        Next lngContLin
        
        'Percorre todas as linhas
        For lngContLin = 2 To lngUltLin
        
            'String com as informa??es concatenadas das colunas D, E e F
            strProcura = .Cells(lngContLin, 4).Value2 & .Cells(lngContLin, 5).Value2 & .Cells(lngContLin, 6).Value2
            
            'Joga os dados na coluna XFD
            .Range("XFD2:XFD" & lngUltLin).Value = vrtConcatenado
            
            'Verifica se a fun??o corresp(match) n?o encontra um erro ao procurar a string
            If Application.WorksheetFunction.CountIf(.Range("XFD2:XFD" & lngUltLin), strProcura) > 0 Then
                .Cells(lngContLin, 8).Value2 = "Conciliado"
            End If
            
            'Apaga os dados na coluna XFD
            .Range("XFD2:XFD" & lngUltLin).Value2 = vbNullString
        Next lngContLin
    End With
End Sub

Enviado: **16 Jul 2019 às 07:49**

Oi Babdallas.

Muito obrigado pela ajuda.

Ao testar verifiquei que está gerando erro de variável não definida na linha abaixo:

With wshConcilia

Enviado: **16 Jul 2019 às 10:25**

wshConcilia não é uma variável, mas sim o codename da planilha. Eu modifiquei o codename da planilha de Plan1 (ou Planilha1, não me lembro direto) para wshConcilia.

Enviado: **16 Jul 2019 às 11:03**

Código: Selecionar todos

Sub InsereTexto()
 Dim LR As Long, b As Range
  Application.ScreenUpdating = False
  ActiveSheet.AutoFilterMode = False
  LR = Cells(Rows.Count, 2).End(3).Row
  For Each b In Range("B2:B" & LR).SpecialCells(2)
   [D1:G1].AutoFilter 1, b.Offset(, -1).Value
   [D1:G1].AutoFilter 2, b.Value
   [D1:G1].AutoFilter 3, (Format(b.Offset(, 1).Value, "###,000.00"))
   If ActiveSheet.AutoFilter.Range.Columns(4).SpecialCells(xlCellTypeVisible).Count > 1 Then
    Range("G2:G" & LR).Value = "Conciliado"
   End If
  Next b
 ActiveSheet.AutoFilterMode = False
 Application.ScreenUpdating = True
End Sub

Enviado: **16 Jul 2019 às 12:46**

Osvaldo Muito obrigado. Funcionou perfeitamente.

Babdallas obrigado também pelo retorno.

Fiz a alteração para With Plan1, porém agora gerou erro 1004 de definição de objeto na linha abaixo:

.Range("XFD2:XFD" & lngUltLin).Value = vrtConcatenado

Pelo o que os objetos estão declarados.

Se me permite pedir mais alguma coisa, pois ao testar a macro do Osvaldo ou aplicar a função do Babdallas verifiquei que se uma linha estiver em branco a validação será "conciliado", pois todas as células das linhas são iguais, então se puder me ajudar mais uma vez para que:

Além do que já foi feito, se a coluna F não tiver valores então a condição toda será falsa e não aparece "conciliado".

Assim em linhas vazias não mais aparecerão "conciliado".

Código: Selecionar todos

'Desenvolvido por Babdallas do guru do excel


Option Explicit

Public Sub ValidarDados2()


    Dim lngUltLin           As Long                                 '?ltima linha preenchida
    Dim lngContLin          As Long                                 'Contador de linhas
    Dim strProcura          As String                               'String com informa??es concatenadas das colunas D, E e F
    Dim vrtConcatenado      As Variant                              'Matriz com os dados para procurar
   
    'With wshConcilia
    With Plan1
    
        lngUltLin = .Cells(.Rows.Count, 1).End(xlUp).Row            '?ltima linha preenchida na coluna A
       
        ReDim vrtConcatenado(1 To lngUltLin - 1, 1 To 1) As Variant
       
        'Limpa coluna H
        .Range("H2:H" & lngUltLin).ClearContents
       
        'Faz loop para a matriz de dados
        For lngContLin = 1 To lngUltLin - 1
            vrtConcatenado(lngContLin, 1) = .Cells(lngContLin + 1, 1).Value2 & _
                                            .Cells(lngContLin + 1, 2).Value2 & _
                                            .Cells(lngContLin + 1, 3).Value2
        Next lngContLin
       
        'Percorre todas as linhas
        
        For lngContLin = 2 To lngUltLin
       
            'String com as informa??es concatenadas das colunas D, E e F
            
            strProcura = .Cells(lngContLin, 4).Value2 & .Cells(lngContLin, 5).Value2 & .Cells(lngContLin, 6).Value2
           
            'Joga os dados na coluna XFD
            
            .Range("XFD2:XFD" & lngUltLin).Value = vrtConcatenado
           
            'Verifica se a fun??o corresp(match) n?o encontra um erro ao procurar a string
            
            If Application.WorksheetFunction.CountIf(.Range("XFD2:XFD" & lngUltLin), strProcura) > 0 Then
                .Cells(lngContLin, 8).Value2 = "Conciliado"
            End If
           
            'Apaga os dados na coluna XFD
            
            .Range("XFD2:XFD" & lngUltLin).Value2 = vbNullString
            
        Next lngContLin
    End With
End Sub

Enviado: **23 Jul 2019 às 13:01**

Olá Senhores.

O Osvaldo me apresentou uma excelente macro que atendeu perfeitamente, porém ao colocar em prática verifiquei que poderia ser melhorada.

Eu tenho pouca experiência com VBA, assim nesse meio tempo tentei aplicar as melhorias, mas não consegui, logo, se possível, gostaria mais uma vez a ajuda de alguém do fórum.

O que eu observei é que se A&B&C e D&E&F estiverem em branco a validação será "conciliado", pois todas as células das linhas são iguais, então:

Além do que já foi feito, se a coluna F não tiver valores então a condição toda será falsa e não aparece "conciliado". A coluna F sempre deve ter um valor.

E outra necessidade minha é se a condição for verdadeira pela validação feita, na coluna H aparecerá a linha em que está a validação de A&B&C. Eu tentei fazer isso com a função corresp, mas não consegui.

Enviado: **23 Jul 2019 às 13:54**

Pegorini escreveu: ... tentei aplicar as melhorias, ...
O que eu observei é que se A&B&C e D&E&F estiverem em branco a validação será "conciliado", pois todas as células das linhas são iguais,...
Não se trata de melhoria, o que você quer é introduzir um novo critério "se houver linhas vazias", critério esse que você não informou antes e não colocou na sua planilha de exemplo.
Veja no arquivo anexado. Inseri linhas vazias em A:C e em D:G, e coloquei conteúdo em F nas linhas inseridas. O código que postei antes apresenta o resultado que eu entendi como o resultado desejado. Verifique. Se houver outras condições possíveis de ocorrerem na sua planilha então coloque TODAS elas e coloque qual o resultado desejado para cada condição.
[/i]
E outra necessidade minha é se a condição for verdadeira pela validação feita, na coluna H aparecerá a linha em que está a validação de A&B&C. Eu tentei fazer isso com a função corresp, mas não consegui.
Depois veremos isso.

Enviado: **23 Jul 2019 às 22:53**

Olá Osvaldo.

Desculpe-me se não me expressei bem. Sou grato por tudo, pois você e demais colegas disponibilizam seu tempo para ajudar terceiros.

Realmente se trata de novo critério, pois quando a gente coloca em prática é que pode aparecer algo não verificado antes.

Se possível ainda me ajudar, destaquei em azul uma condição que não deve ser verdadeira, pois se alguma célula da coluna F não apresentar valor algum a condição será falsa.

Sobre a identificação da linha também não tinha mencionado antes, pois quando coloquei em prática é que verifiquei que isso me ajudaria bastante.

Eu tentei usar aquela sua função com CORRESP, mas não consegui.

Enviado: **23 Jul 2019 às 23:23**

Olá, Pegorini.

Experimente o código abaixo no lugar do anterior. Além da questão da coluna F acrescentei um comando para inserir o número da linha na coluna H.

Código: Selecionar todos

Sub InsereTextoV2()
 Dim LR As Long, b As Range
  Application.ScreenUpdating = False
  ActiveSheet.AutoFilterMode = False
  LR = Cells(Rows.Count, 3).End(3).Row
  For Each b In Range("C2:C" & LR).SpecialCells(2)
   [D1:G1].AutoFilter 1, b.Offset(, -2).Value
   [D1:G1].AutoFilter 2, b.Offset(, -1).Value
   [D1:G1].AutoFilter 3, (Format(b.Value, "###,000.00"))
   If ActiveSheet.AutoFilter.Range.Columns(4).SpecialCells(xlCellTypeVisible).Count > 1 Then
    Range("G2:G" & LR).Value = "Conciliado": Range("H2:H" & LR).Value = b.Row
   End If
  Next b
 ActiveSheet.AutoFilterMode = False
 Application.ScreenUpdating = True
End Sub

Enviado: **24 Jul 2019 às 13:06**

Olá Osvaldo.

Ficou incrível.

Muito obrigado.

Guru do Excel e Power BI

Validação de dados

Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados

Re: Validação de dados