Option Explicit Public Sub ConsolidarDados() Dim wsC As Worksheet, wsF As Worksheet, wsP As Worksheet, wsR As Worksheet Dim nomes As Object, codigosPorCnpj As Object, codigosFiscais As Object Dim ultimaC As Long, ultimaF As Long, ultimaP As Long Dim i As Long, total As Long, linha As Long, pendencias As Long Dim codigo As String, nome As String, cnpj As String, produto As String Dim saida() As Variant, cabecalho(1 To 1, 1 To 4) As Variant Dim telaAnterior As Boolean, telaAlterada As Boolean On Error GoTo Falha Set wsC = ObterAba("Clientes") Set wsF = ObterAba("Fiscal") Set wsP = ObterAba("Compras") ValidarCabecalhos wsC, "Codigo", "Nome do Cliente" ValidarCabecalhos wsF, "Codigo", "CNPJ" ValidarCabecalhos wsP, "CNPJ", "Produto" ultimaC = UltimaLinha(wsC) ultimaF = UltimaLinha(wsF) ultimaP = UltimaLinha(wsP) Set nomes = CreateObject("Scripting.Dictionary") nomes.CompareMode = vbTextCompare Set codigosPorCnpj = CreateObject("Scripting.Dictionary") Set codigosFiscais = CreateObject("Scripting.Dictionary") codigosFiscais.CompareMode = vbTextCompare For i = 2 To ultimaC If LinhaPreenchida(wsC, i) Then codigo = Trim$(CStr(wsC.Cells(i, 1).Value2)) nome = Trim$(CStr(wsC.Cells(i, 2).Value2)) If codigo = "" Or nome = "" Then Err.Raise vbObjectError + 101, , "Clientes: campo vazio na linha " & i If nomes.Exists(codigo) Then Err.Raise vbObjectError + 102, , "Clientes: codigo duplicado na linha " & i & ": " & codigo nomes.Add codigo, nome End If Next i For i = 2 To ultimaF If LinhaPreenchida(wsF, i) Then codigo = Trim$(CStr(wsF.Cells(i, 1).Value2)) cnpj = NormalizarCnpj(wsF.Cells(i, 2).Value2) If codigo = "" Then Err.Raise vbObjectError + 103, , "Fiscal: codigo vazio na linha " & i If Not nomes.Exists(codigo) Then Err.Raise vbObjectError + 104, , "Fiscal: codigo sem cliente: " & codigo If codigosFiscais.Exists(codigo) Then Err.Raise vbObjectError + 105, , "Fiscal: codigo duplicado: " & codigo If codigosPorCnpj.Exists(cnpj) Then Err.Raise vbObjectError + 106, , "Fiscal: CNPJ duplicado: " & cnpj codigosFiscais.Add codigo, True codigosPorCnpj.Add cnpj, codigo End If Next i ' Valida compras antes de alterar qualquer resultado. For i = 2 To ultimaP If LinhaPreenchida(wsP, i) Then cnpj = NormalizarCnpj(wsP.Cells(i, 1).Value2) produto = Trim$(CStr(wsP.Cells(i, 2).Value2)) If produto = "" Then Err.Raise vbObjectError + 107, , "Compras: produto vazio na linha " & i total = total + 1 End If Next i If total = 0 Then Err.Raise vbObjectError + 108, , "Nao ha compras para consolidar." ReDim saida(1 To total, 1 To 4) ' Uma linha por compra: preserva todos os produtos do cliente. For i = 2 To ultimaP If LinhaPreenchida(wsP, i) Then linha = linha + 1 cnpj = NormalizarCnpj(wsP.Cells(i, 1).Value2) produto = Trim$(CStr(wsP.Cells(i, 2).Value2)) If codigosPorCnpj.Exists(cnpj) Then codigo = codigosPorCnpj(cnpj) saida(linha, 1) = codigo saida(linha, 2) = nomes(codigo) Else saida(linha, 1) = "NAO ENCONTRADO" saida(linha, 2) = "NAO ENCONTRADO" pendencias = pendencias + 1 End If saida(linha, 3) = FormatarCnpj(cnpj) saida(linha, 4) = produto End If Next i On Error Resume Next Set wsR = ThisWorkbook.Worksheets("Resultado") On Error GoTo Falha If Not wsR Is Nothing Then If UltimaLinha(wsR) > 1 Then If MsgBox("Atualizar as colunas A:D da aba Resultado? As abas de origem nao serao alteradas.", vbYesNo + vbQuestion, "Tutor IA") <> vbYes Then Exit Sub End If Else Set wsR = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)) wsR.Name = "Resultado" End If telaAnterior = Application.ScreenUpdating Application.ScreenUpdating = False telaAlterada = True If wsR.AutoFilterMode Then wsR.AutoFilterMode = False wsR.Columns("A:D").Clear wsR.Range("A1:D" & total + 1).NumberFormat = "@" cabecalho(1, 1) = "Codigo" cabecalho(1, 2) = "Nome do Cliente" cabecalho(1, 3) = "CNPJ" cabecalho(1, 4) = "Produto" wsR.Range("A1:D1").Value2 = cabecalho wsR.Range("A2").Resize(total, 4).Value2 = saida With wsR.Range("A1:D1") .Interior.Color = RGB(9, 53, 83) .Font.Color = RGB(255, 255, 255) .Font.Bold = True End With wsR.Range("A1:D" & total + 1).Font.Name = "Arial" wsR.Range("A1:D" & total + 1).Font.Size = 11 wsR.Range("A1:D" & total + 1).AutoFilter wsR.Columns("A:D").AutoFit wsR.Activate Application.ScreenUpdating = telaAnterior telaAlterada = False MsgBox total & " compras consolidadas. CNPJs sem cadastro: " & pendencias & ".", vbInformation, "Tutor IA" Exit Sub Falha: If telaAlterada Then Application.ScreenUpdating = telaAnterior MsgBox "Nao foi possivel concluir: " & Err.Description, vbExclamation, "Tutor IA" End Sub Private Function ObterAba(ByVal nomeAba As String) As Worksheet On Error Resume Next Set ObterAba = ThisWorkbook.Worksheets(nomeAba) On Error GoTo 0 If ObterAba Is Nothing Then Err.Raise vbObjectError + 110, , "Aba ausente: " & nomeAba End Function Private Function UltimaLinha(ByVal ws As Worksheet) As Long Dim a As Long, b As Long a = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row b = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row If a > b Then UltimaLinha = a Else UltimaLinha = b End Function Private Function LinhaPreenchida(ByVal ws As Worksheet, ByVal linha As Long) As Boolean LinhaPreenchida = (Len(Trim$(CStr(ws.Cells(linha, 1).Value2))) > 0 Or Len(Trim$(CStr(ws.Cells(linha, 2).Value2))) > 0) End Function Private Sub ValidarCabecalhos(ByVal ws As Worksheet, ByVal a As String, ByVal b As String) If Trim$(CStr(ws.Cells(1, 1).Value2)) <> a Or Trim$(CStr(ws.Cells(1, 2).Value2)) <> b Then Err.Raise vbObjectError + 111, , "Cabecalhos incorretos na aba " & ws.Name & ". Use: " & a & " / " & b End If End Sub Private Function NormalizarCnpj(ByVal valor As Variant) As String Dim texto As String, limpo As String, i As Long, ch As String texto = Trim$(CStr(valor)) For i = 1 To Len(texto) ch = Mid$(texto, i, 1) Select Case ch Case "0" To "9": limpo = limpo & ch Case ".", "/", "-", " ": ' Ignora apenas a pontuacao conhecida. Case Else: Err.Raise vbObjectError + 112, , "CNPJ contem caractere inesperado: " & texto End Select Next i If Len(limpo) <> 14 Then Err.Raise vbObjectError + 113, , "CNPJ deve conter 14 digitos neste exemplo: " & texto NormalizarCnpj = limpo End Function Private Function FormatarCnpj(ByVal cnpj As String) As String FormatarCnpj = Left$(cnpj, 2) & "." & Mid$(cnpj, 3, 3) & "." & Mid$(cnpj, 6, 3) & "/" & Mid$(cnpj, 9, 4) & "-" & Right$(cnpj, 2) End Function