Listar com critério as cidades de MG (VIA VBA)

MarceloAdriano

13-02-2018 17:54:43

Olá Caros integrante Fórum do Támaz Vásques
Tenho um planilha no qual o objetivo é listar todas as cidades tendo como base um critério, por exemplo, a letra "a" assim listasse todas as cidades que inicia-se com a letra "a", até tentei mais em vão - não consegui formular o código em questão...

- Outra dúvida como proceder para usar via vba os intervalos convertidos em tabelas do Excel, e como usar um intervalo renomeado com a função desloc dentro da programação vba?

Segue a tabela em questão

Grato,

IV.JR

13-02-2018 19:05:46

Amigo, veja o exemplo que segue em anexo.

Neste caso os dados não necessariamente precisam estar em ordem alfabética, aliás em qualquer ordem que os dados esteja a macro percorre os dados e retorna apenas as cidades que começam com a letra A como citou no exemplo, existem duas rotinas no módulo que criou uma para manipular o intervalo comum (CitiesToRange) e outra para manipular a tabela (CitiesToListObject) ambas disponíveis através dos botões na planilha.

Manipulando intervalo comum:

Código: Selecionar todos

Public Sub CitiesToRange()
    Dim oDic    As Object
    Dim ws      As Worksheet
    Dim lRow    As Long
    Dim iRow    As Long
    Dim sItem   As String
    Dim arr     As Variant
    
    Set oDic = VBA.CreateObject("Scripting.Dictionary")
    Set ws = Sheet1
    lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    For iRow = 4 To lRow
        sItem = ws.Cells(iRow, 2).Value2
        If VBA.UCase(VBA.Left(sItem, 1)) = "A" Then
            oDic.Add sItem, sItem
        End If
    Next iRow
    
    arr = oDic.Items
    
    ws.Range("C4", ws.Cells(ws.Rows.Count, "C").End(xlUp)).ClearContents
    
    For iRow = LBound(arr) To UBound(arr)
        ws.Cells(iRow + 4, "C").Value2 = arr(iRow)
    Next iRow
    
    MsgBox "Total de " & UBound(arr) + 1 & " cidades encontradas!", vbInformation
End Sub
E para manipular tabela:

Código: Selecionar todos

Public Sub CitiesToListObject()
    Dim oDic    As Object
    Dim ws      As Worksheet
    Dim lo      As ListObject
    Dim iRow    As Long
    Dim sItem   As String
    Dim arr     As Variant
    
    Set oDic = VBA.CreateObject("Scripting.Dictionary")
    Set ws = Sheet1
    Set lo = ws.ListObjects("tbCidades")
    
    For iRow = 1 To lo.ListRows.Count
        sItem = lo.DataBodyRange(iRow, 2).Value2
        If VBA.UCase(VBA.Left(sItem, 1)) = "A" Then
            oDic.Add sItem, sItem
        End If
    Next iRow
    
    arr = oDic.Items
    
    lo.ListColumns(3).DataBodyRange.ClearContents
    
    For iRow = LBound(arr) To UBound(arr)
        lo.DataBodyRange(iRow + 1, 3).Value2 = arr(iRow)
    Next iRow
    
    MsgBox "Total de " & UBound(arr) + 1 & " cidades encontradas!", vbInformation
End Sub
Espero ter ajudado, abraço.

MarceloAdriano

13-02-2018 19:42:00

Olá IV.JR
É tenho muito a aprender...
Como posso interpretar a linha de código: Set oDic = VBA.CreateObject("Scripting.Dictionary")?
Mas de qualquer forma respondeu de forma excelente, era exatamente isso, que procurava!

Grato

IV.JR

13-02-2018 20:01:24

Olá Marcelo,

Fico feliz que tenha te ajudado, comecei há pouco tempo a contribuir nos fóruns!

Então, acontece que eu estou usando um objeto do tipo Dictionary do VBA, basicamente trata-se de um objeto de coleção aonde eu armazeno a cidade apenas se ela começar com a letra A, armazenado estes dados nesta coleção do tipo dicionário, depois eu mando isso para uma Array de dados, ou seja, uma matriz e pra finalizar faço um laço nesta matriz para jogar os dados na planilha.

Existem outros objetos de coleção como o próprio Collection (até menos complexo) que também atenderia, é apenas uma questão de opção mesmo, gosto desse cara.

adribb

13-02-2018 20:30:24

Muito divertido e interessante este fórum, sinceramente, as informações que encontrei aqui me serviram muito, obrigado e saudações.

"Adriana"

MarceloAdriano

13-02-2018 22:26:45

Então IV.JR antes de escrever para o fórum estava com o foco total no código em questão.
É sempre uma boa pratica lidarmos com coleções, array, matrizes e afins pois o código roda mais rápido!
mas através da linha de código, descrito por você:
If VBA.UCase(Left(Ws.Cells(i, 2).Value, 1)) = UCase(Busca) Then
consegui desenrolar o meu projeto inicial, de forma menos complexa que o seu (mas não trabalhando com intervalo ou tabela)
ficou assim o código:
Sub CopiarCidades()
Application.ScreenUpdating = False
Dim LastRows As Long 'achar a ultima linha
Dim LastRows2 As Long
Dim i As Long
Dim Ws As Worksheet
Dim Busca As String
Dim linha As Long

Set Ws = Sheet1
Busca = Sheet1.Range("D5").Value
LastRows = Ws.Cells(Rows.Count, 1).End(3).Row
LastRows2 = Sheet2.Cells(Rows.Count, 1).End(3).Row
Debug.Print LastRows
linha = 4
If Sheet2.Cells(4, 1) <> "" Then GoTo ir
For i = 4 To LastRows
If VBA.UCase(Left(Ws.Cells(i, 2).Value, 1)) = UCase(Busca) Then

'Ws.Cells(i, 1).Copy Sheet2.Cells(Linha, 1)
'Ws.Cells(i, 2).Copy Sheet2.Cells(Linha, 2)
Sheet2.Cells(linha, 1).Value = Ws.Cells(i, 1).Value
Sheet2.Cells(linha, 2).Value = Ws.Cells(i, 2).Value
linha = linha + 1
End If
Next i
'==> efetuar a limpeza do campo
Exit Sub
ir:
For limpar = 4 To LastRows2
'Sheet2.Cells(limpar, 1).ClearContents 'coluna índice
'Sheet2.Cells(limpar, 2).ClearContents 'coluna nome das cidades
Sheet2.Cells(limpar, 1).Resize(, 2).ClearContents
'Selection.ClearContents
Next limpar
Application.ScreenUpdating = True
End Sub



Entretanto, estou buscando ainda desenvolver esse raciocínio. Vou, mais adiante, estudar o seu raciocínio passo a passo para assimilar o conceito.
Obrigado pela ajuda e consideração a minha pessoa.
Grato,
Marcelo Adriano

MarceloAdriano

14-02-2018 11:08:49

Artigo concluído!