Anteriormente no Podcast 2093, mostrei uma classificação VBA simples que funciona se você não estiver classificando por cor. Hoje, Neeta pede ao VBA para classificar os dados do Excel por cor.
A coisa mais complicada sobre a classificação por VBA é descobrir quais códigos de cores RGB você está usando. Em 99% dos casos, você não escolheu uma cor inserindo os valores RGB. Você escolheu uma cor usando esta lista suspensa no Excel.
E, embora você possa usar Fill, More Colors, Custom para saber que a cor selecionada é RGB (112,48,160), isso é um incômodo se você tiver muitas cores.
Então - eu prefiro ligar o gravador de macro e deixar que ele descubra o código. O código gerado pelo gravador de macro nunca é perfeito. Aqui está o vídeo que mostra como usar o gravador de macro ao classificar por cor.
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2186: VBA Classificar por Cor.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje, enviada no YouTube. Eu tinha um vídeo sobre como classificar com VBA, e eles queriam classificar por cores com VBA, o que é muito mais complicado. Eu disse: "Por que você simplesmente não liga o gravador de macro e vê o que acontece?" E, infelizmente, o gravador de macro, você sabe, nos aproxima, mas não nos leva até o fim.
Portanto, Exibir, Macros, Gravar macro, "HowToSortByColor", Armazenar macro nesta pasta de trabalho - perfeito. Clique OK. Tudo bem, agora que o gravador de macro está funcionando, vamos acessar a guia Dados e dizer Classificar. Vamos usar uma caixa de diálogo Classificar e vamos construir isso, certo? Então, vamos dizer que queremos adicionar um nível, Classificar na cereja, mas não Classificar nos valores da célula; vamos classificar na cor da célula - a cor da célula é a cor de preenchimento - e queremos colocar o vermelho no topo e, em seguida, copiar esse nível e colocar o amarelo em segundo lugar; e então adicionaremos um novo nível - iremos para a coluna D, a coluna de data - Classifique pela cor da célula, vermelho primeiro, copie esse nível, amarelo e então aqui; e então, aqui em Elderberry, coluna E, há algumas fontes azuis que eu não quero ver como era,então vamos adicionar isso como uma cor Classificar na fonte com azul no topo; e se tudo isso for um empate sem nenhuma cor, adicionaremos um nível final apenas na coluna A - Valores de célula, do maior para o menor; e clique em OK.
Tudo bem, agora, algumas coisas - não pule a próxima etapa - seu arquivo, agora, garanto que está armazenado como xlsx. Este é um ótimo momento para fazer Arquivo, Salvar como e salvá-lo como xlsm ou xlsb. Se você não fizer isso, todo o seu trabalho até este ponto será perdido quando você salvar este arquivo. Eles excluirão as macros de qualquer coisa armazenada em xlsx. Tudo bem?
Então, paramos de gravar lá e queremos dar uma olhada em nossas macros. Então, você pode fazer isso com View, Macros-- View, Macros-- e encontrar a macro que acabamos de gravar-- HowToSortByColor-- e clicar em Edit. Tudo bem, então aqui está nossa macro e, ao olhar para isso, o problema que temos é que hoje temos 25 linhas mais um título. Então, vai para a linha 26. E eles codificaram que sempre vão olhar para as linhas 26.
Mas pensando sobre isso, especialmente em comparação com o antigo VBA para classificação, não temos que especificar todo o intervalo - apenas uma célula na coluna. Portanto, em qualquer lugar onde houver coluna C26, irei reduzi-la para apenas dizer: "Ei, não, olhe para a primeira célula dessa coluna." Então E2, e então, aqui, A2. Então, no meu caso, eu tinha 1, 2, 3, 4, 5, 6, níveis de classificação - 6 coisas para alterar.
E então esta é a parte que o gravador de macro fica muito, muito ruim, é que eles só vão classificar as linhas 26 o tempo todo. Então, vou mudar isso. Vou dizer: "Olhe, comece no intervalo A21 e estenda-o para .CurrentRegion." Vamos dar uma olhada no Excel e ver o que ele faz. Portanto, se eu apenas escolher qualquer célula - A1 ou qualquer coisa - e pressionar Ctrl + *, ele seleciona a região atual. Ok, vamos fazer isso. Aqui, do meio, Ctrl + *, e o que isso faz, é se estender em todas as direções até atingir a borda da planilha, no topo da planilha, ou na borda direita dos dados ou na borda inferior dos dados . Então, dizendo A1 .CurrentRegion, é como ir para A1 e pressionar Ctrl + *. Tudo bem? Então, aqui você tem que mudar isso. Agora, tudo o mais na macro está bem; isto'tudo vai funcionar. Eles obtiveram SortOnCellColor e SortOnFontColor e xlSortOn. Não preciso me preocupar com nada disso; tudo o que tenho que fazer é olhar aqui e ver se eles codificaram a região que iriam usar para o intervalo, codificaram até onde eles foram e não precisa ser codificado. E com esse passo simples, mudando esses seis itens e o sétimo item, temos algo que deve funcionar.
Agora, vamos fazer o teste. Vamos voltar aqui para o Excel e adicionaremos algumas novas linhas na parte inferior. Vou colocar 11s lá, e adicionaremos alguns vermelhos - um vermelho, um amarelo e aqui um azul. Tudo bem. Então, se formos executar este código - executar este código, então eu clico aqui e clico no botão Executar - e, em seguida, voltar, devemos ver que aquele 11 se tornou o item superior em vermelho, ele apareceu lá no amarelos e aparecem no blues, então está tudo funcionando perfeitamente. Por que foi para o topo? Porque aconteceu que a última classificação é a coluna A e, portanto, quando há um empate, parece que a coluna A é o critério de desempate. Então, esse código está funcionando.
Para aprender a escrever VBA, eu, junto com Tracy Syrstad, escrevi uma série de livros, Excel VBA e MACROS. Já houve uma edição para 2003, 2007, 2010, 2013 e 2016; em breve 2019. Certo, então, encontre a versão que corresponde à sua versão do Excel e isso o ajudará na curva de aprendizado.
Conclusão: o episódio de hoje é Como usar o VBA para classificar por cor. A maneira mais fácil de fazer isso, especialmente porque você não sabe quais códigos RGB foram usados para cada uma das cores - você apenas escolheu vermelho, você não sabe qual é o código RGB e não quer olhar - ligue o gravador de macro usando Exibir, Macros, Gravar nova macro. Depois de fazer a classificação, clique em Parar gravação - no canto inferior esquerdo - Alt + F8 para ver uma lista de macros, ou Exibir, Macros, Exibir macro - a guia Exibir, Macros e em seguida, Exibir macros - isso é confuso. PSelecione sua macro e clique em Editar, e sempre que vir C2 para alguns números de intervalo, apenas altere para apontar para a linha 2. E então, onde eles especificam o intervalo a ser classificado, Range ("A1"), CurrentRegion, irá expandir. Tudo bem.
Bem, ei, quero agradecer a você por passar por aqui, vejo você na próxima vez para outro netcast de.
No vídeo, configurei uma classificação de seis níveis. A caixa de diálogo final é mostrada aqui:
No dia em que gravei a macro, eu tinha 23 linhas de dados mais um título. Havia sete locais na macro que codificavam o número de linhas. Isso deve ser ajustado.
Para cada nível de classificação, existe um código como este:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) É uma tolice que o gravador de macro especifica C2: C24. Você só precisa especificar uma célula na coluna, portanto, altere a primeira linha acima para:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Faça uma mudança semelhante para cada um dos níveis de classificação.
Perto do final da macro gravada, você tem o código gravado para realmente fazer a classificação. Começa assim:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithEm vez de apenas classificar A1: E24, altere o código para começar em A1 e se estender até a região atual. (A região atual é o que você obtém se pressionar Ctrl + * em uma célula).
.SetRange Range("A1").CurrentRegionO código final mostrado no vídeo é:
Sub HowToSortByColor() HowToSortByColor Macro ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear ' Sort column C by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort Column C by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column D by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort column D by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column E by blue font ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("E2"), _ xlSortOnFontColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(0, 176, 240) ' Sort Column A by Values descending ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending, _ DataOption:=xlSortNormal ' Perform the Sort With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1").CurrentRegion .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End SubNota
É provável que sua pasta de trabalho tenha sido salva com uma extensão XLSX. Faça um Salvar como para mudar para uma extensão XLSM ou XLSB. Todas as macros salvas em XLSX são excluídas.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Uma maçã por dia mantém o VBA afastado."
Tom Urtis
