Melhor prencher uma Tabela com Formulas ou VBA?.

Question

Bom Dia.

Tenho um calendario conforme mostra a Fig 1 com nomes dos funcionarios e na direita os dias do calendario de um mes com seus respectivos dias de semanas que muda conforme altera a data na celula K3.

Entre a coluna do nome e do calendario, tem colunas ocultas do cadastro dos funcionarios(esta resumido somente com dados de dias que cada funcionario pode vir trabalhar durante a semana, sendo que D e dia de descanco), conforme mostra a Fig 2.

O calendario, pretendo deixar como uma Tabela entre as colunas K5 e AO20 pois tem formatacoes e validacoes de dados, ..........ou talvez incluindo o cadastro tambem, nao sei ainda qual e mais viavel, e deixar os dias de semana como cabecario da Tabela. Quando for mes de fevereiro que nao tem dias 28 ou 29 em diante, fiz uma formatacao para mudar de cor para nao ficar visivel.

Na Fig 2 entre as colunas C e I tem dados do dia que cada funcionadio esta de folga com letra "D" e a medida que preenchesse os dados dos funcionarios, aparecessem os dias de folgas automaticamente conforme dia da semana do mes, para isso fiz formulas conforme mostra na Fig 3 em K6 e copiei para toda Tabela, mas queria fazer um botao para que preenchesse automaticamente com codigo VBA que acho ser mais coveniente porque com formulas, preciso transformar depois em valores.

Teria uma forma de preencher o calendario com  descanco "D" conforme dia da semana desejada de cada funcionario em VBA?

Desde ja Agradeco.

Tadao

Fig. 1

Fig2

Fig 3

Answer 1

Boa tarde Tadao.

Existem inúmeras possibilidades de fazer isso, muitas mesmo. Como não sei qual forma te atenderia melhor, segue uma sugestão bastante simples que pode adaptar para o seu caso:

Dim Intervalo As String, Campo As Range, Dia As Long
Dim Ci As Long, Li As Long
Dim pPlan As Worksheet


Intervalo = "K6:AO10"
Ci = 3      'Coluna com Domingo - 1º Dia (Tabela resumida)
Li = 5      'Linha com os dias da semana (Tabela resumida)

Set pPlan = ActiveSheet

With pPlan
    .Range(Intervalo).ClearContents
    For Each Campo In .Range(Intervalo)
    
        Dia = Weekday(.Cells(Li , Campo.Column))
        Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
    Next
End With

Note que para este método funcionar a linha onde está o dia da semana (Linha 5) deve ser necessariamente uma data (para exibir "Sun", por exemplo, é necessário formatar a data como "ddd", mas não é permitido usar uma fórmula de "Texto" para isso). Neste caso também a linha 4 de sua tabela é desnecessária para o funcionamento. Caso não queira definir o intervalo na sua Macro pode-se usar o Range de sua Tabela (ListObject), sendo redimensionado automaticamente.

Abraço.

---

Filipe Magno

Answer 2

Obrigado pelo pronto retorno Felipe Magno.

Fiz as modificacoes conforme orientacao (Fig 4)e funciomou beleza, obrigado.......porem quando mudo o mes na celula k3 para outro mes da erro, acho que tem que fazer o "intervalo" dinamico no VBA conforme muda a data da celula K3,nao e?.

Ainda nao modifiquei o calendario para Tabela como desejo, pois conforme voce disse e tenho notado tambem, o cabecario da Tabela nao pode ser formula, mas sim um texto.

Como o usuario disse-me que o mes comercial que ele utiliza sempre comeca com dia 1, entao fiz o titulo do calendario comecando com 1 ate 31 que e o maior numero de dias do mes, e quando tem meses com menor numero de dias, fiz formatacao para que mudasse a cor da letra com o mesma cor do fundo da celula para ficar invisivel conforme mostra a Fig 3(tem a formula que utilizei nas celulas) .

Gostaria de transformar o calendario em Tabela pois nas celulas do calendario vai ter que colocar validacoes de dados e formatacao de dados, ou seja no dias que estao em branco preciso preencher com horas que o funcionario ira trabalhar pois tem varios turnos.

Por exemplo tem turno que entra as 21Horas e outro que entra as 24 horas, e nas celulas preciso fazer validacoes de dados para que nao digite dados errados pois depois teria que filtrar para cada dia que vai passando, , elabolar uma lista com nomes de cada turno para entregar para o motorista de onibus fazerem a coleta.

Daria para fazer a rotina, para que lesse os dias da semana na linha 4 e deixar a linha 5 como cabecario, e fazer o "intervalo" do programa ficar dinamico conforme muda o mes na celula K3?

Fig 4

Answer 3

Boa noite Tadao.

Primeiramente desculpe pela demora, mas não deu para responder antes. Vamos à questão:

"acho que tem que fazer o "intervalo" dinamico no VBA conforme muda a data da celula K3,nao e?"

Exatamente, você terá que utilizar uma fórmula. Daí você inicialmente esbarrará no ponto que você mesmo mencionou: Tabelas não aceitam fórmulas nos títulos. Mas a solução para isso é extremamente simples: basta usar uma linha qualquer acima da tabela, como a linha 4 ou 3. Se preferir esta linha nem precisa ficar visível. Se quiser usar a mesma solução que propus para a linha 4, por exemplo:

Troque:

Dia = Weekday(.Cells(Li , Campo.Column))

Por:

Dia = Weekday(.Cells(Li-1 , Campo.Column))

Ou crie uma variável para indicar a linha.

Desta forma te atende?

---

Filipe Magno

Answer 4

Obrigado pelo retorno Felipe.

Como a linha 4 acima da Tabela do calendario ja esta com datas, conforme Fig 5, fiz a modificacao no codigo conforme sua sugestao e funcionou beleza,Obrigado.

Deixei os Dias sem formulas e utilizei como cabecario da tabela que chamei de "calendario" e na linha 4 que tem os dias de semanas coloquei as datas que mudam conforme muda a celula K3, sendo que a coluna 4 formatei para aparecer os dias da semana.

Na linha 4 coloquei formulas para que nao aparecessem dias do mes seguinte, deixando em branco quando vira o mes conforme mostrado na linha 26 da Fig 5, e na linha 5 formatei de tal forma que se a linha 4 for "" mudasse a cor com a mesma cor de fundo da celula para ficar invisivel.

Abaixo a formula na celula L4 que copiei ate AO4.

=IFERROR(IF(MONTH(K4+1)=MONTH(K4),K4+1,""),"")

Sendo assim o codigo ficou como na fiura 6, mas teria como mudar para que nao preenchesse quando os dias estiverem em branco?

Na realidade a figura 5 e como a figura 7, cheia de validacoes e formatacao onde nas datas voce me ajudou a resolver um problema que quando protegia a planilha nao saia a seta da formatacao.

Atualmente tem o cadastro dos funcionarios numa planilha separada como mostra a figura 8, e agora estou tentando fazer uma planilha que engloba o cadastro e o calendario juntos na mesma planilha. Nao entendo muito bem de arquitetura de sistema(se e assim que se chama) entao fui fazendo sem muita nocao e virou uma torre de Badel......rs. Se puder......trocar de ideia........seria de grande ajuda,ne.

Tadao

Fig 5

Fig 6

Sub preencher()

Dim Intervalo As String, Campo As Range, Dia As Long
Dim Ci As Long, Li As Long
Dim pPlan As Worksheet

Application.ScreenUpdating = False

Intervalo = "calendario" 'calendario e nome da Tabela
Ci = 3      'Coluna com Domingo - 1o Dia (Tabela resumida)
Li = 5      'Linha com os dias da semana (Tabela resumida)

Set pPlan = ActiveSheet

With pPlan
    .Range(Intervalo).ClearContents
    For Each Campo In .Range(Intervalo)
    
        Dia = Weekday(.Cells(Li, Campo.Column))
        Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
    Next
End With
Application.ScreenUpdating = True

End Sub

Fig 7

Fig 8

Answer 5

Boa noite Tadao.

Uma solução simples seria um pequeno teste:

Dia = .Cells(Li - 1, Campo.Column)
If Dia > 0 Then
    Dia = Weekday(Dia)
    Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
End If

Sobre a estrutura de seu projeto não sei se consigo te ajudar sem entender toda a estrutura de sua pasta, mas posso dizer que sempre costumo separar por abas as diferentes funções de cada massa de dados. Ou seja, eu normalmente faria o cadastro em uma aba própria e aproveitaria as informações nas demais abas, como você já está fazendo.

Teria como pontuar melhor onde está com dúvida?

Abraço.

---

Filipe Magno

Answer 6

Ola Felipe, desculpe pela demora da resposta, e que estava tentando entender o codigo que voce fez antes de responder.

Os codigos que os profissionais fazem, sao suscinto, bem resumidos, de ficar de boca aberta, eu ainda estou no arroz com feijao....rs, nao sei as boas praticas de programacao.

Para fazer o que voce fez precisa estar conciente de como o For Each movimenta a cada ciclo,nao? Para saber a posicao que esta o For Each a cada ciclo, voce utilizou o Campo.Column, muito legal, e colocou dentro do cells() para ler o dia da semana na posicao varredura atual,nao?, tecnica muito bom.

Dia = .Cells(Li - 1, Campo.Column)
If Dia > 0 Then
    Dia = Weekday(Dia)
    Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
End If

Nesse codigo acima, nao entendi bem o que voce quis fazer com Dia=Weekday(Dia), se puder explicar ficaria grato.

Na primeira linha Dia= .Cells(Li-1,Campo.Column , achei que faltou o Weekday() como tinha antes.

Acho que entendi o que voce estava pretendendo, entao criei uma variavel String DiaS para armazenar os dias da linha 4, e quando o mes for de 30 dias ou 28 dias, que estaria em "", preencho o Campo.Value2="". Nunca usei o Value2, sempre usei o Value, mas qual seria o objetivo de usar o Value2?. Sei que o Value2, no caso de datas, coloca o numero serial da data, mas no caso no intervalo estou so colocando Textos e numeros.

Dessa forma, o codigo ficou como abaixo Fig 9, nao sei se poderia melhorar.

Fig 9

Sub preencher()

Dim Intervalo As String, DiaS As String, Campo As Range, Dia As Long
Dim Ci As Long, Li As Long
Dim pPlan As Worksheet

'Application.ScreenUpdating = False

Intervalo = "calendario" 'calendario e nome da Tabela
Ci = 3      'Coluna com Domingo - 1o Dia (Tabela resumida)
Li = 5      'Linha com os dias da semana (Tabela resumida)

Set pPlan = ActiveSheet
On Error Resume Next
With pPlan
    .Range(Intervalo).ClearContents
    For Each Campo In .Range(Intervalo)
        DiaS = .Cells(Li - 1, Campo.Column)
        Dia = Weekday(.Cells(Li - 1, Campo.Column))
        
        If DiaS = "" Then
            Campo.Value2 = ""
        Else
            Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
        End If
                
            Next
        End With
        Application.ScreenUpdating = True

End Sub

Outra coisa que gostaria de saber se possivel, na Fig 5 tem uma Tabela de nome calendario no intervalo K6:AO19 e defini como Tabela tambem como cadastro o intervalo A6:I19. Tem jeito de fazer de tal meneira que se adicionar nomes na celula A20 da Tabela cadastro, a Tabela calendario tambem adicione uma linha copiando as formatacoes e validacoes?.

Depois do calendario, tem colunas de Avaliacoes de cada funcionario contando qtos dias Descansou(Ds.), Trabalhou no dia do seu descanso(T.Ds.) saiu mais cedo do trabalho(S.Cd.) Faltou Sem aviso(FS) Falta Comunicada()FC, que e contada com formulas nas colunas AP:AT., e para que copie a formula automaticamente quando acrescenta mais nomes no cadastro, inclui a area de avaliacao junto com a Tabela de calendario.

Teria como mudar o codigo da Fig 9 para que o quando apertasse o botao de preencher, nao preencha essas colunas de avaliacao que tem formulas?

Fig 10

O que me ocorreu nesse momento, para nao preencher as colunas de Avaliacao, foi de colocar mais um Else, ou seja ElseIf Dias="Tot." ja que na linha 4 depois do calendario tem celulas com "Tot." e pulei com Goto. Bem que Goto, dizem que nao e uma boa pratica de programacao.....(nao existe um Exit If ?.....) ficando como na Fig 11.

Acrescentei um For Next para limpar so as colunas do calendario, ja que sei que o calendario tem 31 dias, e as colunas com formulas da avaliacao nao deleto.

Teria como melhorar essa rotina para que o For Each nao varresse as colunas da avaliacao, ou seja delimitar o Range(Intervalo) so na area do calendario?

Fig 11

Sub preencher()

Dim Intervalo As String, DiaS As String, Campo As Range, Dia As Long
Dim Ci As Long, Li As Long
Dim pPlan As Worksheet

'Application.ScreenUpdating = False

Intervalo = "calendario" 'calendario e nome da Tabela
Ci = 3      'Coluna com Domingo - 1o Dia (Tabela resumida)
Li = 5      'Linha com os dias da semana (Tabela resumida)

Set pPlan = ActiveSheet
On Error Resume Next
With pPlan

    For i = 1 To 31
        .Range(Intervalo).Columns(i).ClearContents
    Next
    
    For Each Campo In .Range(Intervalo)
        DiaS = .Cells(Li - 1, Campo.Column)
        Dia = Weekday(.Cells(Li - 1, Campo.Column))
        
        If DiaS = "" Then
            Campo.Value2 = "-"
        ElseIf DiaS = "Tot." Then
            GoTo Proximo
        Else
            Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
        End If
        
Proximo:

    Next
End With
Application.ScreenUpdating = True

End Sub

Quanto a sua pergunta

Sobre a estrutura de seu projeto não sei se consigo te ajudar sem entender toda a estrutura de sua pasta, mas posso dizer que sempre costumo separar por abas as diferentes funções de cada massa de dados. Ou seja, eu normalmente faria o cadastro em uma aba própria e aproveitaria as informações nas demais abas, como você já está fazendo.

Teria como pontuar melhor onde está com dúvida?

O que faco hoje, e que tenho os cadastros dos homens e mulheres em planilhas separadas como na figura 12 e sempre no inicio do mes atualizo a Tabela do cadastro acrescentando ou mandando para a planilha do arquivo morto os funcionarios desligados e depois colo os dados necessarios para a planilha do calendario, fazendo o mesmo com cadastro das mulheres. Em vez de ficar copiando e colando, achei melhor colcocar do lado do calendario e acrescentaria novos funcionarios diretamente no calendario e quando quisesse so editar o cadastro, com um botao deixaria o Tabela calendario oculto e caso quisesse editar o calendario deixaria, com um botao  a Tabela do cadastro oculto. Sera que isso nao e uma boa pratica ou maneira de programacao?

Fig 12

Desde ja agradeco a sua atencao

Tadao

Answer 7

Boa noite Tadao.

Vamos por partes, foram muitas perguntas:

"Nesse codigo acima, nao entendi bem o que voce quis fazer com Dia=Weekday(Dia), se puder explicar ficaria grato."

Esta função retorna o dia da semana de determinada data. Como não passei o segundo parâmetro, a função retorna '1' para 'domingo', '2' para 'segunda' e assim por diante.

"Na primeira linha Dia= .Cells(Li-1,Campo.Column , achei que faltou o Weekday() como tinha antes."

Observe que primeiro obtenho o valor que está escrito na linha 4, uma vez que sua estrutura insere 'vazio' quando não for uma data de interesse (resultado de sua fórmula). Ou seja, caso 'Dia=0' significa que esta coluna deve ser ignorada. Note que não é necessário inserir vazio para as células destas colunas, pois nada é escrito quando 'Dia=0'.

"Nunca usei o Value2, sempre usei o Value, mas qual seria o objetivo de usar o Value2?. Sei que o Value2, no caso de datas, coloca o numero serial da data, mas no caso no intervalo estou so colocando Textos e numeros."

Por padrão eu sempre utilizo 'Value2', que garante que seja escrito o valor real da célula, sem formatações e em muitos casos é muito mais rápido do que qualquer outro método (depende do tipo de dados). Eu utilizo o 'Value' quando estritamente necessário, como quando desejo inserir uma data formatada na planilha, assim não corro o risco de esquecer. Obs.: isto vale tanto para leitura quanto escrita na planilha.

"Tem jeito de fazer de tal meneira que se adicionar nomes na celula A20 da Tabela cadastro, a Tabela calendario tambem adicione uma linha copiando as formatacoes e validacoes?."

Eu não inseriria linhas manualmente em nenhuma Tabela. O mais simples é criar uma função para redimensionar as Tabelas, solicitando ao usuário quantas linhas ele deseja. No seu caso, após informar o n° de linhas, a função inseriria linhas nas duas tabelas. O código básico para redimensionar é:

pPlan.ListObjects(SuaTabela).Resize Range(Cells(Li, Ci), Cells(Lf, Cf))

"Teria como mudar o codigo da Fig 9 para que o quando apertasse o botao de preencher, nao preencha essas colunas de avaliacao que tem formulas?"

Se você utilizar o código que te passei na resposta anterior basta que a linha 4 (ou outra qualquer que você desejar) não tenha nada escrito. O código vai naturalmente ignorá-las no processo de escrita. Porém, o processo de limpar a tabela terá que ser alterado, pois está apagando toda a tabela. Neste caso creio que o código que utilizou resolve o problema.

Sobre a estrutura de sua pasta, creio que sua estrutura atual não seja problemática, pois a quantidade de dados parece muito reduzida. Mas sinceramente não sei lhe afirmar com certeza se é a melhor solução ou não para o seu caso, pois isso dependeria de um conhecimento melhor sobre todo o processo. Mas uma coisa lhe digo: ficar copiando e colando dados de um lado para o outro não é algo muito prático, pois além de trabalhoso e tedioso, aumenta muito as chances de erro.

Espero ter conseguido responder suas dúvidas, mas qualquer coisa é só perguntar.

---

Filipe Magno

Answer 8

Obrigado pelo retorno Felipe.

Realmente na fase atual.....tenho muitas duvidas a perguntar......e o Forum esta sendo de muita ajuda,obrigado.

Agora entendi o porque nao estava funcionando a sua sugestao; e que quando o mes nao tem dias ate 31, a minha formula da linha 4 estava preenchendo com "" e nao com 0(zero) como mostra a Fig 5. Mudei a formula para que em vez de "" colocasse zero, dai funcionou beleza........mas depois do calendario na mesma linha tem cabecario da avaliacao....entao deu erro novamente. Entao ficou duas opcoes, fazer conforme sua sugestao e deixar as colunas da avaliacoes sem titulo na linha 4 ou deixar assim e usar o codigo da Fig11.

Mas mesmo com  os dois metodos, o For Each vai varrer toda a Tabela, ate onde nao desejo que varresse, se desse para delimita como For Each Campo in Range(intervalo).Columns(1:31)....????nao tem alguma forma para delimitar a area da Tabela?. Sera que nesses caso em vez de For Each e melhor usar o For Next?

Entendi bem a utilizacao do Value2, vou procurar a prestar mais atencao quanto a aplicacao..... Value,... alias,Valeu!...duplamente ,obrigado.

A planilha que e preenchido, tem mais ou menos 100 nomes, e para fazer a programacao dos dias diponiveis de cada funcionario, demora muito e a sua rotina vai facilitar o preenchimento.

Eu chamei de cadastro, mas na realidade eh parte do cadastro que eh utilizado nesse calendario de Shift Holiday, e hoje ela e feita numa planilha a parte como na figura 8(Tabela) e depois de feita as alteracoes, eh colado na planilha do Shift Holiday da Fig 7(Nao e Tabela). Como eh 1 para 1 pensei em colocar tudo numa planilha ficando duas Tabelas, uma de cadastro ao lado da Tabela de calendario.

Quando tem novo funcionario, digito na ultima linha da Tabela, dai as formulas , validacoes e formatacoes serao copiadas da linha anterior, ficando facil de digitar. A sua recomendacao de nao colocar dados diretamente na Tabela, eh para evitar aluma precaucao?. Nesse caso teria que fazer um botao e digitar a qt de linhas necessarias, e qd colocar linhas a mais teria que faser outro botao para deletar, nao?

Na Fig. 13 tentei fazer um codigo para qdo preencher a ultima linha do cadastro inserisse uma linha no calendario, nao sei se tem algum Bug, mas...daria para fazer um cometario, se poderia melhorar?

Fig 13

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(myrange, Target) Is Nothing Then
        Application.EnableEvents = False
        Call adcultreg
        Application.EnableEvents = True
    End If
End Sub
Function myrange() As Range
Set myrange = ActiveSheet.ListObjects(1).ListRows.Item(ActiveSheet _
    .ListObjects(1).ListRows.Count).Range.Columns(1)
End Function
'-----------------------------------------------------
Sub apagaultreg()
Dim Tb As ListObject
Set Tabela = ActiveSheet.ListObjects(2)

Tabela.ListRows(Tabela.ListRows.Count).Delete
End Sub
'-----------------------------------------------------
Sub adcultreg()
Dim Tb As ListObject
Set Tb = ActiveSheet.ListObjects(2)
Tb.ListRows.Add

End Sub
'-----------------------------------------------------

A Fig 14 abaixo,seria um modelo da Fig 7(real), que na realidade, a Tabela de cadastro (Fig 14), alem dos dias disponiveis de trabalho por semana dos funcionarios, teria dados como local de busca do onibus da empresa, dia de vencimento do passsaporte, telefone, e-mail etc..(Fig 8) e a cada dia que passa no calendario da Fig 14,faz aparecer um quadro vermelho(no caso esta no dia 27) onde no lado do calendario depois das colunas de avaliacaoes, faco uma lista dos nomes dos funcionarios e horarios de trabalhos desse dia, para poder fazer uma planilha de lista de funcionarios e horarios com local de recolhimento para o motorista do onibus conforme Fig 15. Para que a linha de producao nao de problema de falta de gente, e enviado todo dia confirmacao de trabalho por e-mail, e caso o funcionario nao possa vir, procurar substituto para esse dia. Depois de ter as listas de funcionarios disponiveis nesse dia, e feita a distribuicao de tarefa na linha de producao conforme Fig 16, onde o Felipe Gualberto ajudou a fazer uma rotina. No calendario tem as validacoes de dados que seriam caso de fallta sem comunicar de FS, falta comunicada FC, programado para entrar as 21 e chegou atrasado de 21A, saiu mais cedo do que o expediente de 21C, se era dis de descanso e atendeu pedido para substituir um faltante de 21T..etc, e na coluna de avaliacoes sao computados quantos dias cada funcionario fez essas acoes durante o periodo, para que no proximo mes nao admita esses funcionarios. Estou falando funcionarios mas na realidade sao temporarios estudantes universitarios estrangeiros que por lei nao podem trabalhar a semana inteira por lei trabalhista local.

Desculpe se escrervi muito , e gostaria que me alertasse se estiver fora do escopo, ou se deveria parciar em varias postagens.

Desde ja agradeco.

Tadao

Fig 14

Fig 15

Fig 16

Answer 9

Boa noite Tadao.

Vamos novamente por partes:

"Agora entendi o porque nao estava funcionando a sua sugestao; e que quando o mes nao tem dias ate 31, a minha formula da linha 4 estava preenchendo com "" e nao com 0(zero) como mostra a Fig 5"

Na verdade a fórmula deve funcionar com "" sim, uma vez que a variável 'Dia' foi declarada como 'Long', ou seja, se a célula está vazia o seu valor atribuído passa a ser zero.

"Mas mesmo com  os dois metodos, o For Each vai varrer toda a Tabela, ate onde nao desejo que varresse, se desse para delimita como For Each Campo in Range(intervalo).Columns(1:31)....????nao tem alguma forma para delimitar a area da Tabela?. Sera que nesses caso em vez de For Each e melhor usar o For Next?"

Não há problema em varrer toda a tabela, pois no código que sugeri nada será feito se não for uma coluna desejada. De toda forma, se quiser impedir a análise destas colunas, basta simplesmente verificar qual coluna está, com um teste adicional (coloquei o código resumido, você deve adaptá-lo):

    Dim CiTab As Long
    CiTab = .Range(Intervalo).Column
    
    For Each Campo In .Range(Intervalo)
        
        If Campo.Column - CiTab + 1 <= 31 Then
            Dia = .Cells(Li - 1, Campo.Column)
            If Dia > 0 Then
                Dia = Weekday(Dia)
                Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
            End If
        End If
    Next

"A sua recomendacao de nao colocar dados diretamente na Tabela, eh para evitar aluma precaucao?"

Não exatamente. Eu sempre trabalho com planilhas protegidas (sem senhas), para garantir a integridade do projeto. Da forma que sugeriu, ou teria que trabalhar com ela desprotegida ou teria que ficar desprotegendo e protegendo a todo momento, o que é muito ruim. Por isso automatizo da forma que sugeri.

" Nesse caso teria que fazer um botao e digitar a qt de linhas necessarias, e qd colocar linhas a mais teria que faser outro botao para deletar, nao?"

Está aí uma operação que sempre evito: remover linhas fisicamente de tabelas. Mas se for necessário possivelmente teria que ter uma macro para o processo.

"Na Fig. 13 tentei fazer um codigo para qdo preencher a ultima linha do cadastro inserisse uma linha no calendario, nao sei se tem algum Bug, mas...daria para fazer um cometario, se poderia melhorar?"

Não creio que monitorar alterações na planilha seja a melhor alternativa, mas pode ser que funcione. Aliás, está funcionando como deseja? Como lhe disse, ainda acho mais simples inserir as linhas sob demanda.

"A Fig 14 abaixo,seria um modelo...

Desculpe se escrervi muito , e gostaria que me alertasse se estiver fora do escopo, ou se deveria parciar em varias postagens."

Realmente é um pouco complicado tentar entender seu projeto apenas desta forma, há muitos detalhes. Acho mais interessante pontuarmos melhor suas dúvidas, mesmo que sejam conceituais. Indo direto ao ponto é mais fácil que mais pessoas consigam te ajudar. Mas fique tranquilo, não está fora do escopo, pergunte sempre que precisar. E quando o foco da pergunta for diferente do título do post é mais interessante abrir um novo, já que assim pode ajudar e receber ajuda de mais pessoas.

Se tiver mais perguntas ou se não respondi adequadamente fique a vontade para perguntar novamente.

Obs.: não sou especialista, nem sempre terei as melhores respostas, sou apenas um entusiasta nesta linguagem.

Abraço.

---

Filipe Magno

Answer 10

Obrigado pelas palavras de conforto e  humildade, assim fico mais a vontade para poder perguntar para o meu aprendizado.

Fiz o teste com a rotina que voce descreveu e parou justamente quando lia a celula AM4 que tem formula que retorna um "" por ser mes de Fevereiro conforme mostra a Fig. 17.

O Codigo na integra esta na Fig 18, onde devo ter errado?

Fig 17

Fif 18

Sub teste1()
Dim Intervalo As String, Campo As Range, Dia As Long
Dim Ci As Long, Li As Long
Dim pPlan As Worksheet
Dim CiTab As Long

Intervalo = "calendario" 'calendario e nome da Tabela
Ci = 3      'Coluna com Domingo - 1o Dia (Tabela resumida)
Li = 5      'Linha com os dias da semana (Tabela resumida)
Application.EnableEvents = False

Set pPlan = ActiveSheet
With pPlan
    .Range(Intervalo).ClearContents
     CiTab = .Range(Intervalo).Column
   
     For Each Campo In .Range(Intervalo)
        
        If Campo.Column - CiTab + 1 <= 31 Then
            Dia = .Cells(Li - 1, Campo.Column)
            If Dia > 0 Then
                Dia = Weekday(Dia)
                Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
            End If
        End If
    Next
    
    
End With
End Sub

Realmente quando tem Tabelas com formulas e o usuario delete sem querer da problema,nao?

Ja tive caso assim e utilizei um evento para que rodasse a desprotecao so quando ativasse a primeira celula da ultima coluna da Tabela e depois outro evento quando sae dessa celula.

Essa sua sugestao de delimitar a Tabela com 

If Campo.Column - CiTab + 1 <= 31 Then

se funcionar, seria muito bom pois pelo que vejo podemos delimitar as linhas tambem,nao?

Por exemplo, na Fig 14, se apertar o botao preencher, ira sempre preencher todas as linhas com dados de programacao da disponibilidade de trabalho de todos os funcionarios, ou seja queria que preenchesse so as linhas com novos nomes de funcionarios que entrou no meio do mes digitados na Tabela cadastro, porque a cada dia que passa, e atualizado o estado de cada funcionario, se faltou ou chegou atrasado ou saiu mais cedo...etc. sendo computado a avaliacao dos funcionarios nas colunas de avaliacao.

Talvez....fazer uma coluna auxiliar onde  teria um Yes/No e sempre que apertar o botao preencher, so preencheria as linhas em que a coluna auxiliar tiver um Ok e depois de preencher colocar um No nessa coluna auxiliar para, na proxima vez nao preencher de novo. Criaria um botao de reset para mudar a posicao de No para Yes na coluna auxiliar quando da virada do mes.

Depois talvez, numa outra postagem......se eu nao conseguir fazer, pedir ajuda para ordenar as tres tabelas ao mesmo tempo, ou seja precisa ficar linkado senao os dados do funcionario na Tabela cadastro irao ficar desalinhados com dados do calendario,nao?

Sempre tenho procurado pontuar nas postagens, aprendendo os pequenos detalhes, pois estes pequenos detalhes despercebidos que fazem grandes estragos,nao?. Mas hoje pressinto falta de estruturar o sistema tambem, e como fosse um maestro que nao sabe tocar instrumentos, mas o musico que sabe tocar, nao sabe maestrar. Nao sei se isso chama Arquitetura de programacao ou sistema, voce sabe de alguns sites que eu possa aprofundar?

Desde ja agradeco pela atencao

Tadao

Answer 11

Boa noite Tadao.

"Fiz o teste com a rotina que voce descreveu e parou justamente quando lia a celula AM4 que tem formula que retorna um "" por ser mes de Fevereiro conforme mostra a Fig. 17.

O Codigo na integra esta na Fig 18, onde devo ter errado?"

Me desculpe, o erro foi totalmente meu. Eu não testei com o resultado da fórmula que você está utilizando e acabei não prevendo que o resultado seria diferente. De toda forma sugiro que faça uma pequena alteração em sua fórmula:

=SE(E(MÊS(K4+1)=MÊS(K4);K4<>0);K4+1;0)

É necessário fazer o teste de nulidade porque caso contrário a segunda coluna à direita terá o valor "1", a terceira '2' e assim sucessivamente, o que resultará em erro na lógica. Mais uma vez desculpas.

Aproveitando, lembre-se de adaptar o código para apagar apenas o intervalo desejado. Como dica eu sugeriria, no entanto, criar um intervalo nomeado com estas 31 colunas, assim não precisaria fazer estes ajustes e seu código poderia ficar mais simples. Já pensou nesta possibilidade?

"Realmente quando tem Tabelas com formulas e o usuario delete sem querer da problema,nao?"

É por este motivo que permito a edição apenas nas células que não possuem fórmulas, onde o usuário (ou eu mesmo, rsrsrs) não "consiga" estragar nada.

"Essa sua sugestao de delimitar a Tabela com (...) se funcionar, seria muito bom pois pelo que vejo podemos delimitar as linhas tambem,nao?"

Certamente. É possível inserir quantas condições quiser. Mas lembre-se, um bom código não deve necessitar de intervenção no seu uso normal. Ou seja, o processo de inserir, alterar e remover registros na sua planilha não deve exigir que você altere seu código manualmente, como nomes dos intervalos, por exemplo.

"Talvez....fazer uma coluna auxiliar onde  teria um Yes/No (..)"

É uma excelente estratégia. Deixa seu código mais seletivo de maneira bastante prática.

"ordenar as tres tabelas ao mesmo tempo, ou seja precisa ficar linkado senao os dados do funcionario na Tabela cadastro irao ficar desalinhados com dados do calendario,nao?"

Está aí uma tarefa que pode ser desastrosa. Eu tentaria desviar deste tipo de estratégia a qualquer custo. Não há como fazer tudo numa única tabela e delimitar melhor o intervalo que a macro irá atuar? Lembre-se que você pode criar intervalos nomeados dentro de sua Tabela e desde que contemplem toda a coluna, estes intervalos são automaticamente redimensionados com sua tabela.

"voce sabe de alguns sites que eu possa aprofundar?"

Infelizmente não muitas referências. Sempre passo aqui no fórum e no site do Felipe Gualberto (http://ambienteoffice.com.br/), mas a maior parte acabo descobrindo por demanda e curiosidade.

Espero que as respostas seja úteis.

Grande abraço.

---

Filipe Magno

Answer 12

Bom dia Felipe, as suas respostas estao sendo de grande utilidades para o meu aprendizado,Obrigado.

Fiz a modificacao na formula conforme sugeriu e funcionou beleza, so fiz uma alteracao pois em fevereiro depois 

do dia 28 a formula coloca zero mas nos dias seguintes colocam 1 e 2, entao mudei a formula para =IF(AK4=0,0,IF(MONTH(AK4+1)=MONTH(AK4),AK4+1,0)).

Essa Dica de apagar com intervalo nomeado e uma boa ideia,nao?. Fiz um intervalo nomeado dinamico com offset como mosta na Fig 19 chamado de ArCal e defini como =OFFSET(IniTab,0,0,COUNTA(Sheet1!$A:$A)-3,31), sendo o IniTab a celula nomeada L6 que e o inicio da tabela e funcionou beleza.

Sera que nao da para selecionar, por exemplo usando o codigo application. intersect nas linhas da coluna J que esta como "Yes" usando Union e rows(activecell.row)select e fazer a interseccao com colunas que deseja colocar dados so dos novos nomes com For Each?

Estou tentando usar, ou forcando a usar a Tabela para poder aprender um pouco mais das suas aplicacoes, mas no caso de ordenar as tres tabelas ao memo tempo e um problema,nao?., entao em vez de usar Tabelas sera que nao e melhor usar regioes nomeadas e definir como dinamica? como na Fig. 20?

Tadao

 

 Fig 19

Fig 20

Fig 21

With pPlan
    'deleta os dados do calendario com intersect
    
    
    

    'deleta os dados do calendario com Nome da Area Dinamica
    
    Range("ArCal").ClearContents
    
'    'deleta os dados do calendario com For Next
'    For i = 1 To 31
'        .Range(IntCal).Columns(i).ClearContents
'    Next
'    celula inicial da Tabela calendario
    CiTab = .Range(IntCal).Column

Answer 13

Boa noite Tadao.

" Fiz um intervalo nomeado dinamico com offset "

Eu particularmente não gosto de intervalos dinâmicos, mas é apenas uma questão de gosto talvez. Eu utilizaria um intervalo fixo mesmo, aproveitando que quando estão em colunas inteiras em tabelas eles são automaticamente redimensionados com a tabela (basta garantir que englobem toda a coluna). Mas se funcionou, ok.

"Sera que nao da para selecionar, por exemplo usando o codigo application. intersect nas linhas da coluna J que esta como "Yes" usando Union e rows(activecell.row)select e fazer a interseccao com colunas que deseja colocar dados so dos novos nomes com For Each?"

Eu faria apenas um teste condicional, mantendo a mesma estrutura, acho mais simples:

For Each Campo In .Range(Intervalo)
   
   If Campo.Column - CiTab + 1 <= 31 And .Cells(Campo.Row, Ci + 7).Value2 = "Yes" Then

Você também poderia fazer o teste em outro "If" separado, como preferir.

" entao em vez de usar Tabelas sera que nao e melhor usar regioes nomeadas e definir como dinamica? como na Fig. 20?"

Insisto ainda que acho melhor utilizar intervalos fixos (dica: selecionando com o teclado é mais simples, basta ir na primeira coluna, pressionar 'Ctrl+Barra de Espaço' + 'Shift+Seta Direita' até a coluna desejada).

Abraço.

---

Filipe Magno

Answer 14

Obrigado pelo pronto retorno Felipe.

No primeiro codigo que voce sugeriu onde tem a variavel 

Intervalo = "K6:AO10"

eu substitu por 

Intervalo = "calendario" 'calendario e nome da Tabela

ou seja apenas emprestei o nome da Tabela para a variavel intervalo, assim quando a Tabela mudar de tamanho, o a area nomeada muda dinamicamente nao precisando de mudar o endereco do codigo, nao?

Como dito, emprestei so o nome da Tabela para poder usar o nome definido da tabela ficar dinamica, nao estou usufluindo nada das funcoes da Tabela, por isso achei a sua ultima Dica de Nomear Area, uma boa , no lugar de ficar usando varias Tabelas.

O usuario que vai utilizar essas planilhas, nao e bem entendido do Excel, entao pensei em deixar o mais automatico possivel para agilizar o trabalho dele.

Mas no caso de eu usar, achei interessante pressionar 'Ctrl+Barra de Espaço' + 'Shift+Seta Direita' , vou comecar a utilizar,obrigado.

Selecionando dessa maneira, voce estaria selecionando a coluna inteira da planilha, certo? Se por exemplo for usar  no For Each, iria varrer ate a ultima celula da coluna, no caso da tabela, iria ate a ultima linha do nome  nomeada da Tabela, entao pensei em selecionar por exemplo so as linhas que tem na coluna J com dados Yes que intersecta as colunas do calendario de dia 1 ate dia 31, depois nao daria para usar o For Each Campo in Selection ?, dai a rotina iria varrer e colcar os dados so onde deseja, e no fim da rotina colocar um No na celula da coluna J para a proxima vez nao preencher essa linha. Tentei fazer a rotina mas nao consegui.

Talvez em vez de ficar complicando muito por uma pequena velocidade na execucao, sera que e  preferivel usar a sua ultima sugestao do Codigo?

Por ultimo, caso voce puder dar uma opiniao a respeito,..... quando usa o Vlookup(pesquisa,area,coluna) por exemplo, se a area for uma lista de A1:A20 , mas se um dia a lista aumentar ira dar erro, entao tem sites que falam para usar uma lista nomeada dinamica com Offset que para muitos semileigos e dificil de entender, mas em outras literaturas falam para que quando a lista for variavel, usar A;A ,ou seja a coluna inteira.....testei os dois casos e nao percebi diferenca, entao pra que complicar, nao?. Deve ser que nem o caso de espaconave americana e russa, o do americano e cheio de trictric...o do russo e simples e robusto e mais barato.....os dois funcionam e atingem o mesmo objetivo.

Tadao

Answer 15

Ola Felipe, testei o codigo da sua sugestao e ficou perfeito, aproveitei e coloquei no final da rotina para que antes de pular para a linha seguinte mudar o dado da celula de controle coluna J de "Yes" para "No".

Vou usar essa solucao que esta mais enxuto. e muito obrigado pelas longas explanacoes tecnicas.

Tadao

    For Each Campo In .Range(IntCal)
    
        If Campo.Column - CiTab + 1 <= 31 And .Cells(Campo.Row, Ci + 7).Value2 = "Yes" Then
        
            Dia = .Cells(Li, Campo.Column)
            If Dia > 0 Then
                Dia = Weekday(Dia)
                Campo.Value2 = .Cells(Campo.Row, Ci + Dia - 1)
            End If
        End If

        'muda a celula de controle de Yes para No
        If Campo.Column = 48 Then Campo.Offset(0, -38).Value = "No"
       
    Next

Answer 16

Bom dia Tadao.

"Por ultimo, caso voce puder dar uma opiniao a respeito,..... quando usa o Vlookup(pesquisa,area,coluna) por exemplo, se a area for uma lista de A1:A20 , mas se um dia a lista aumentar ira dar erro, entao tem sites que falam para usar uma lista nomeada dinamica com Offset que para muitos semileigos e dificil de entender, mas em outras literaturas falam para que quando a lista for variavel, usar A;A ,ou seja a coluna inteira.....testei os dois casos e nao percebi diferenca, entao pra que complicar, nao?."

Reforçando: se você utilizar o atalho que sugeri e nomear o intervalo selecionado ele será dinâmico, ou seja, quando você redimensionar a tabela ele será automaticamente redimensionado. Então você pode usar o nome deste intervalo no 'For Each' (Intervalo="SeuNome") e sua região ficará delimitada e atualizada automaticamente. Faça o teste.

Sobre o código em si, tenha em mente que esta é uma das soluções possíveis, que pode ou não ser a ideal dependendo do caso. Se a tabela for muito extensa, talvez outra abordagem seja melhor, em função do desempenho. Mas isso depende de cada caso. Só não se esqueça de desabilitar a atualização de tela, faz muita diferença.

Que bom que o código ajudou, grande abraço.

---

Filipe Magno

Answer 17

Ola Felipe, em primeiro lugar muito obrigado pelas longas explanacoes dispensadas.

Eu nao sei se esta postagem ainda esta aberta, mas se tiver e se puder, gostaria de mais um esclarecimento.

Pelo que entendi voce usou a variavel CiTab = .Range(IntCal).Column para nao ficar um endereco da coluna como endereco relativa em relacao a planilha, certo?(por favor, me corrija se estiver dizendo coisa errada), desse modo mesmo que deslocar a Tabela para outro local sempre vai retornar a coluna inicial da Tabela, que achei muito legal.........mas quando inseri uma coluna na coluna A, a rotina nao funcionou mais, acho porque o teste do If Campo.Column - CiTab + 1 <= 31 And .Cells(Campo.Row, Ci + 7).Value2 = "Yes" Then onde voce colocou Ci + 7 o Ci voce definiu como 3, que na realidade o domingo comeca na coluna 3, mas depois que inclui mais uma coluna, deveria ser coluna 4,Ok?, entao para evitar ficar mudando sempre que tem alteracoes durante o desenvolvimento, utilizei a mesma tecnica de deixar o endereco como relativo colocando Ci = .Range(IntCad).Column + 2 e funcionou beleza.

A pergunta seria, mas se eu precisar de aumentar mais uma coluna dentro da Tabela entre a coluna do Sexo e Domingo, teria que alterar o valor de Ci = .Range(IntCad).Column + 2 para Ci = .Range(IntCad).Column + 3,certo?, nao teria um codigo que retornasse nao a coluna do inicio da Tabela, mas sim que retornasse a coluna que esta o nome do cabecario Sun da Tabela cadastro?Dessa maneira, mesmo aumentando colunas dentro da Tabela(como esta em fase de desenvolvimento), a rotina nao teria problema.

Tem como obter o endereco da coluna de uma celula ativa "activecell" dentro da Tabela, em relacao a tabela?. Por exemplo na Fig 19 se a celula L5 estiver ativa retornasse A ou 1 , pois seria a primeira coluna da tabela calendario. 

Voce poderia explicar porque o codigo .DataBodyRange.ListRows("2:4").Select funciona mas o  .DataBodyRange.ListColumns("2:4").Select nao funciona? Mas se colocar letras correspondentes em vez de numeros como .DataBodyRange.ListColumns("B:D") dai funciona, nao sei porque.

P.S.No lugar de .ListRows e .ListColumns , o certo seria .Rows e .Columns

Gostaia que me alertasse caso se eu deveria colocar em nova postagem.

Desde ja agradeco.

Tadao

Answer 18

Boa noite Tadao.

Primeiramente desculpe pela demora. Praticamente não utilizei o computador em casa nos últimos dias.

Vamos às suas dúvidas: "Pelo que entendi voce usou a variavel CiTab = .Range(IntCal).Column para nao ficar um endereco da coluna como endereco relativa em relacao a planilha, certo?"

Sim. Esta é uma forma de reduzir a necessidade de intervenção no código.

"mas depois que inclui mais uma coluna, deveria ser coluna 4,Ok?"

Como já disse, o código pode ser mais ou menos complexo dependendo das condições que deseja atender. No código original eu parti do pressuposto que o design de sua tabela já estava pronto, ou seja, se precisar alterar como o exemplo que citou é necessário alterar o código. É possível desenvolver formas de evitar esta intervenção manual, mas tudo depende do trabalho que deseja dispensar no código aliado à flexibilidade que necessita na planilha.

"nao teria um codigo que retornasse nao a coluna do inicio da Tabela, mas sim que retornasse a coluna que esta o nome do cabecario Sun da Tabela cadastro?"

Existem várias formas: você poderia utilizar um "For" para procurar, ou a função "Match". Ou ainda poderia utilizar um intervalo nomeado. As formas são muitas e a melhor depende de cada caso. Apenas lembre-se que "Sun" pode ser uma String ou uma data cuja máscara é o dia da semana.

"Tem como obter o endereco da coluna de uma celula ativa "activecell" dentro da Tabela, em relacao a tabela?. Por exemplo na Fig 19 se a celula L5 estiver ativa retornasse A ou 1 , pois seria a primeira coluna da tabela calendario. "

Range(Tabela).Column    'Retorna a primeira coluna da tabela
ActiveSheet.ListObjects(1).Active   'Verdadeiro se a célula ativa estiver dentro da 1ª Tabela criada
Tab.Active    'Verdadeiro se a célula ativa estiver dentro da Tabela Tab (utilizar Set para atribuir)

"Voce poderia explicar porque o codigo .DataBodyRange.ListRows("2:4").Select funciona mas o  .DataBodyRange.ListColumns("2:4").Select nao funciona? "

Creio que é uma característica interna. Você poderia tentar contornar com (dentro do parêntesis):

chr(64+2) & ":" & chr(64+4)

Abraço.

---

Filipe Magno

Answer 19

Ola Felipe, obrigado pelo retorno.

Agradeco pelas explanacoes para reduzir intervencao no codigo.Quando esta na fase de projeto e tem que mudar muito as Tabelas conforme a necessidade do usuario, ficar intervindo no codigo da uma trabalheira,nao?.

O Sun(celula C5) da Tabela cadastro ou o 1(celula L5) da Tabela calendario eu estou usando String. Fui muito infeliz em dar como exemplo a celula L5 que e justamente o cabecario do inicio da Tabela e para isso realmente a sua sugestao:

 Range(Tabela).Column 'Retorna a primeira coluna da tabela

...retorna o que deseja, mas vejamos o exemplo da Fig 22 em que quero selecionar os dados do cabecario Col 2 que e a segunda coluna da Tabela e rodo a rotina que esta dentro da Fig e seleciona os dados do Col 2 que desejo, mas se precisar de aumentar mais uma coluna com nome ce cabecario como Col xyz e rodar a mesma rotina , vai selecionar dados que nao desejo, pois desejo os dados da coluna Col 2, para tal teria que fazer intervencao no codigo o que gostaria de evitar. Nao teria uma forma de refenciar, nao o numero da coluna da Tabela mas o nome da coluna da Tabela?, dessa forma, onde quer que esteja a columa de nome Col 2 retornaria dados que desejo,nao?. Tentei tambem nomear a celula D6 do cabecario ,dai mesmo aumentando masi colunas dentro da Tabela, posso referenciar a celula nomeada......mas tem algum codigo que retorne o numero da coluna da Tabela de um determinada celula nomeada?. Voce disse em usar For ou Match... e para fazer uma funcao que retorne isso?

Quanto a sua sugestao de referenciar com 

chr(64+2) & ":" & chr(64+4)

...e uma otima tecnica,nao?, vou fazer uso dessa dica quando necessario, muito obrigado.

Tadao

Fig 22

Fig 23

Answer 20

Boa noite Tadao.

Perceba que escolhendo utilizar o nome da Coluna você acaba caindo no mesmo problema, talvez até um pouco pior: se a coluna for renomeada (até mesmo pelo usuário final), seu código deixará de funcionar igualmente. Lembrando que é bem pouco provável que o usuário final insira uma coluna em sua tabela. Se a posição desta coluna for realmente necessária desta forma, eu sugeriria criar uma linha de controle oculta acima de sua tabela e procurar nesta linha (com um 'For', por exemplo) por exemplo, assim saberia que esta linha não seria alterada acidentalmente.

De toda forma, pesquisei um pouco e descobri uma forma de usar o nome da coluna, e é extremamente simples:

Tab1.ListColumns(["Col2"]).Range.Select

Basta usar o nome diretamente como acima.

Qualquer coisa estou a disposição.

Abraço.

---

Filipe Magno

Answer 21

Obrigado pelo retorno,Felipe.

Quanto a mudanca do Cabecario pelo usuario final, eu precavi de tal maneira que deixo as celulas do cabecario e onde tem formulas, protegidas e quando roda a rotina, eu  desprotejo no inicio e no final volto a proteger.

Se criar uma linha de controle e mesmo dando para procurar com for, nao saberia se essa celula corresponderia a qual coluna da Tabela.

A formula que voce descobriu, e o que eu estava a procura, tentei de varias formas mas nao tinha funcionado...Obrigado Felipe.

Estava ate pensando em usar a funcao nativa com WorksheetFunction que uma vez utilizei para um evento que qdo sae da planilha sem preencher determinada coluna obrigatoria, voltasse para a planilha, forcando o usuario preencher como mostra a Fig 24

Fig 24

Sub VerificaSePreenchido()

If WorksheetFunction.CountBlank(Range("Tabela1[[col xyz]:[Col 2]]")) = 0 Then
    MsgBox "Preenchido"
Else
    MsgBox "Tem Dados Vazio"
End If

End Sub

Mas nao sei como transformar essa chamada em um .Select

A sua formula funciona para uma coluna, e eu estava tentando como na Fig 25, para ver se funcionasse em faixas de colunas ou varias colunas, mas deu Zebra.

Fig 25

Sub seleciona()

Dim Tab1 As ListObject
Set Tab1 = ActiveSheet.ListObjects(1)
Tab1.ListColumns(2).Range.Select
Tab1.ListColumns(["Col 2"]).Range.Select 'Funciou...obrigado Felipe
'Tentativas sem sucesso..

'Tab1.ListColumns([["Col 4"]:["col 5"]]).Range.Select 'Zebra para varias colunas
'Tab1.ListColumns([["Col 4:col 5"]]).Range.Select 'Zebra para varias colunas
'Tab1.ListColumns([["Col 4"&":"&"col 5"]]).Range.Select 'Zebra para varias colunas

End Sub

O modo de escrever o codigo da Fig 24 esta funcionando, e ai tem a faixa da Range como abaixo, no If........

Range("Tabela1[[col xyz]:[Col 2]]")

.....entao, como a sua sugestao de Range("Tabela1").Column funciona retornando a primeira coluna, ou seja 1, conforme a Fig 26, o modo de escrever o codigo logo abaixo de Range("Tabela1[Cab 2]").Column tambem deveria funcionar e retonar o numero da coluna referente a Tabela1,nao e?......mas deu Zebra....

Tadao

Fig 26

Sub selciona()

MsgBox Range("Tabela1").Column ' a sua sugestao funciona

MsgBox Range("Tabela1[Cab 2]").Column ' esse daqui deu Zebra...


End Sub

Answer 22

Olá.

"Estava ate pensando em usar a funcao nativa com WorksheetFunction que uma vez utilizei para um evento que qdo sae da planilha sem preencher determinada coluna obrigatoria, voltasse para a planilha, forcando o usuario preencher como mostra a Fig 24"

Por quê ao invés de fazer essa fórmula no VBA você não insere ela na planilha e utiliza o resultado no código? Seria extremamente mais simples e elegante! Fica a dica.

"A sua formula funciona para uma coluna, e eu estava tentando como na Fig 25, para ver se funcionasse em faixas de colunas ou varias colunas, mas deu Zebra."

Dica: caso precise selecionar vários intervalos não contíguos você pode usar o "Union":

Union(Tab1.ListColumns(["Col2"]).Range, Tab1.ListColumns(["col xyz"]).Range).Select

Você também pode atribuir a um objeto, utilizando o "Set". Não sei se resolve para o seu caso, mas acho que a fórmula na planilha é a melhor alternativa.

"Range("Tabela1[Cab 2]").Column tambem deveria funcionar e retonar o numero da coluna referente a Tabela1,nao e?......mas deu Zebra...."

Desta forma funciona:

Debug.Print ActiveSheet.ListObjects(1).ListColumns(["Col2"]).DataBodyRange.Column

Dica: atribua a um objeto utilizando o "Set" e investigue as informações que ele possui. Foi assim que encontrei o comando acima:

Set x = ActiveSheet.ListObjects(1).ListColumns(["Col2"])

Abraço.

---

Filipe Magno

Answer 23

Ola Felipe obriado pelo retorno.

Nao entendi bem o que voce quis dizer com:

Por quê ao invés de fazer essa fórmula no VBA você não insere ela na planilha e utiliza o resultado no código? Seria extremamente mais simples e elegante! Fica a dica.

Utilizar na planilha e usar o resultado?.... O que quis mostrar e que a faixa que utilizei na formula nativa 

Range("Tabela1[[col xyz]:[Col 2]]")

funcionou e no VBA nao estava funcionando........... mas notei que coloquei a aspa em local errado e agora esta funcionando, mas se colocar variavel, nao funciona, nao sei porque.

Sub seleciona()

Dim Tab1 As ListObject, Ret As String
On Error GoTo erro

Set Tab1 = ActiveSheet.ListObjects(1)
Ret = InputBox("Digite a Coluna Desejada : ")
'
Tab1.ListColumns([Ret]).Range.Select 'Funciona com Variavel Ret

MsgBox "Selecionou a coluna indicada na variavel Ret: " & Ret

Range("tabela1[[Col 6]]").Select 'Selecionou a coluna indicada no codigo"

MsgBox "Selecionou a coluna indicada no Codigo: Col 6"

Range("tabela1[[Col 3]:[Col 6]]").Select 'Selecionou a faixa da coluna indicada no codigo"

MsgBox "Selecionou a faixa da coluna indicada no Codigo: Col 3 ate 6"


Range("tabela1[[Ret]]").Select 'Deu erro com variavel


Exit Sub

erro:

MsgBox "Coluna nao Encontrada"

End Sub

O codigo que voce indicou para uma coluna, funciona com variavel, como mostra acima. Eu gostaria de usar variavel para definir em que colunas colocar determinada validacao. e se for usar a sua sugestao, como voce disse, teria que usar Union, e para isso queria usar o For Each com variavel String e declarar uma Colecao de String e rodar....????mas sera que existe colecao de string? Fiz uma postagem perguntado como carregar com string uma colecao....mas acho que nao e bem assim.... desculpe a minha ignorancia em VBA.

Nao sei bem onde procuar as informacoes corretas, utilizo o Google para pesquisar, mas o que voce disse sobre 

atribua a um objeto utilizando o "Set" e investigue as informações que ele possuiDigito Set VBA Excel no Google?

Tadao

Answer 24

"Nao entendi bem o que voce quis dizer com:

Por quê ao invés de fazer essa fórmula no VBA você não insere ela na planilha e utiliza o resultado no código? Seria extremamente mais simples e elegante! Fica a dica."

O que quis dizer é que você pode inserir algumas fórmulas na planilha (como o contar em branco que você utilizou) e passar para o código já o resultado da fórmula, uma vez que fórmulas nas planilhas são imunes a movimentações de células e alteração de nomes em Tabelas.

Range("tabela1[[Ret]]").Select 'Deu erro com variavel

Tente assim:

Range("Tabela1[" & [Ret] & "]").Select

"atribua a um objeto utilizando o "Set" e investigue as informações que ele possuiDigito Set VBA Excel no Google?"

Para fazer o que sugeri basta ir na janela "Variáveis Locais" com o código em modo depuração, como na imagem abaixo:

Abraço.

---

Filipe Magno

Answer 25

Ola Felipe obrigado pelo retorno.

A sua sugestao abaixo, funciona funciona muito bem Obriado.

Range("Tabela1[" & [Ret] & "]").Select

Baseado nisso ...tentei uma faixa como...Range("tabela1[" & [Ret] & ":" & [Ret1] & "]").Select.....para tentar selecionar uma faixa com variaveis,dai deu erro. Acho que tem que usar o Union, mesmo, nao?

Desculpe o meu pouco conhecimento na aera, ou talvez por ter esquecido o portugues por estar muito tempo fora do pais....mas ainda nao entendi o caso de colocar a formula na planilha e passa para o codigo. O que sei e fazer a macro na planilha para gerar o codigo......mas algumas vezes o codiggo nao sae.

Obrigado por ter colado a figura da janela de variaveis locais....mas ainda nao sei como aparecer essa janela....o meu office esta com menus em japones, mas andei clicando em tudo, e procurando na met mas nada...So sei que no editor, quando aperta o F2 aparecce uma janela que ainda nao sei a utilizacao dela.

Tadao

Answer 26

Olá, boa noite.

Tente:

Range("Tabela1[[" & [Ret] & "]:[" & [Ret1] & "]]").Select

Dessa forma para mim funcionou.

"Desculpe o meu pouco conhecimento na aera, ou talvez por ter esquecido o portugues por estar muito tempo fora do pais....mas ainda nao entendi o caso de colocar a formula na planilha e passa para o codigo. "

Não se preocupe, está tudo certo. Vou tentar explicar novamente: quando sugeri isto você estava tentando verificar se havia alguma célula em branco no intervalo, certo? O que sugeri foi você utilizar uma fórmula de planilha em determinada célula de controle (Local oculto ao usuário) e utilizar o resultado desta fórmula no seu código. Ex.:

=CONTAR.VAZIO(Tabela1[col xyz]:Tabela1[Col2])

Suponha esta fórmula em A1 (se você mover ou trocar o título da tabela nenhum problema ocorrerá, pois a fórmula atualiza automaticamente). No seu código você poderia utilizar:

If ActiveSheet.Range("A1").Value2 = 0 Then
    MsgBox "Preenchido"
Else
    MsgBox "Tem Dados Vazio"
End If

Apenas isso. Compreendeu?

"O que sei e fazer a macro na planilha para gerar o codigo......mas algumas vezes o codiggo nao sae."

Isso é verdade, nem sempre temos o resultado esperado, mas é assim mesmo, infelizmente.

"mas ainda nao sei como aparecer essa janela"

Está no Menu Exibir >> Janela 'Variáveis Locais'.

"So sei que no editor, quando aperta o F2 aparecce uma janela que ainda nao sei a utilizacao dela."

Nesta janela você pode pesquisar nas bibliotecas do VBA ou mesmo do seu projeto, como o valor de constantes, a localização de funções, etc. Ela se chama "Pesquisador de Objetos".

Veja o exemplo abaixo:

Eu pesquisei a palavra "Criterios" dentro do meu projeto (VBAProject). Ele me retornou todos os módulos que tem essa palavra-chave, assim como o conteúdo de cada módulo deste. No detalhe é possível ver que dentro do módulo "mPublico" existe uma constante chamada "Criterios0G" que tem como valor a string "Criterios0G". Compreendeu?

À medida que seu projeto se torna mais complexo esse tipo de ferramenta pode te ajudar bastante, mas não estranhe se não sentir falta. Dependendo do projeto não ajuda muito.

Abraço.

---

Filipe Magno

Answer 27

Ohhh Felipe, esse ai tambem funcionou beleza. Tentei de varias formas, mas nunca ia imaginar desse jeito, isso tem uma regra ou e na base da tentativa?

Range("Tabela1[[" & [Ret] & "]:[" & [Ret1] & "]]").Select

A Figura que voce mandou valeu muito, diz que uma figura ou desenho fala mais que mil palavras...como as posicoes do menu do office sao os mesmos, fica facil de entender, obrigado...............porem nao tive o mesmo resultado.

Na mesma posicao tem um icome escrito ローカルウィンドウ que traduzindo e Janela Local e aparece como na figura abaixo, uma janela que e difereente do que voce colou, o que devo estar fazendo de errado?

Tadao

Answer 28

"Tentei de varias formas, mas nunca ia imaginar desse jeito, isso tem uma regra ou e na base da tentativa?"

"Tabela1[" & [Ret] & "]"
Retorna: Tabela1[col xyz]

"Tabela1[[" & [Ret] & "]" & ":[" & [Ret1] & "]" & "]"
Retorna: Tabela1[[col xyz]:[Col2]]

Tem regra sim: observe acima o resultado da composição. Perceba que o intervalo está totalmente definido dentro da "Tabela1" (Colchetes inicial e final) e que cada coluna tem seu próprio par de colchetes, da mesma forma que faria se tivesse que escrever a fórmula na planilha. O resultado da sua composição inicial seria: "tabela1[col xyz:Col2]". Note a diferença.

".porem nao tive o mesmo resultado."

Teve sim Tadao. É a mesma janela. Ocorre que quando não está em modo depuração (ponto de interrupção ativo) ela fica vazia mesmo. Note que na minha figura o código está interrompido, por isso ela tem valores.

Abraço.

---

Filipe Magno

Answer 29

Mais uma vez, obrigado pelo retorno Felipe.

Quanto a regra, acho que entendi,....eh parecido com o que declara no MsgBox,nao?. Os textos colocam entre Aspas, e os nomes das variaveis sem Aspas concatenando com &, e no caso de Tabelas os colchetes fazem partes do nome da variavel, por isso devem colcocar & fora dos colchetas das variaveis como & [NomeVariavel] &. Obrigado.

Consegui fazer aparecer a tela conforme sua orientacao, uma tela que vejo pela primeira vez conforme figura abaixo, mas como pesquisar de que os codigos de faixas de Tabelas precisam daquelas concatenacoes complicadas?

Tadao

Answer 30

Tadao,

esta é a Janela das variáveis locais do código em execução. Quando te sugeri utilizá-la para pesquisa em objetos note que atribui, com a instrução "Set", todo o objeto "ListObject". Esta é uma das utilidades desta janela.

No seu caso, perceba que não há nenhuma variável na linha que você deseja, então nada será exibido nesta janela. De toda forma, para o que você deseja no momento há duas soluções bastante práticas:

1. Selecione apenas a parte entre parênteses, incluindo as aspas. Aperte Shift+F9. Você verá a string resultante.
2. Copie apenas a parte entre parênteses, incluindo as aspas. Vá na Janela de "Verificação Imediata" (atalho: Ctrl+G). Digite "?" sem as aspas e em seguida cole o texto copiado. Você verá a string resultante.

Abraço.

---

Filipe Magno

Answer 31

Ola Felipe, obrigado por mais um retorno e pela paciencia e dedicacao.

Vi com mais detalhes a sua explanacao e finalmente consegui o mesmo feito, obrigado.

Estive lendo e estudando todas as Dicas que voce escreveu, e comecei a entender a filosofia de uma boa maneira de programacao.

Pelo que entendi, usar o maximo as formulas nativas na planilha, e deixar protegito para nao criar problemas com o usuario final. Caso tenham casos mais complexos, fazer uma rotina usando o maximo os resultados das formulas nativas da planilha.Fazer uma planilha auxiliar para deixar dados provisorios ou temporarios usados nas rotinas e ocultar com senhas a planilha auxiliar. Nomear areas e usar os nomes nas formulas ou rotinas.

Tadao

Answer 32

Ola Felipe, obrigado pelas longas explanacoes que foram muito util para o meu aprendizado.

Tenho ate receio de fazer mais uma pergunta por ja estar abusando da sua boa vontade......mas....

Se puder, gostaria de trocar uma ideia sobre a rotina que voce fez para preencher os dados, que esta funcionando beleza. Nao falei a quantidade de registros que normalmente e usado, para facilitar a expanacao da postagem.

Geralmente e usado uns 120 registros(Nomes), carreguei os nomes e rodei e demora de 2 a 3 segundos para preencher com o For Each. Sera que se usar o Copiar e Colar demora menos tempo? Por exemplo na fig 27 tem a Tabela de nome ShiHody onde tem dois intervalos sendo uma de Cadastro e outa de Calendario, fazendo uma copia do DataBodyRange do cabecario Sun do lado do intervalo de cadastro, e depois selecionar todos os cabecarios de dias que tem Sun e colar, repetindo o mesmo para o Mon..ate Sat?. O problema e que do lado do calendario o cabecario(nem poderia ser pois ficaria com nomes duplicados) onde deseja colar sao dias, e as semanas correpondentes a esses dias esta acima fora da Tablela com datas formatadas para apresentar dias da semana.

Sera que uma rotina desse jeito ficaria muito complicado?

Tadao

Fig 27

Answer 33

Ola Felipe, estive pensando numa solucao e resovi fazer da maneira abaixo onde esta como Procedimento para preencher tudo, e vou usar a sua rotina para Preencher novos registros com Yes.

Se nao tiver nenhum comentario, acho que vou deixar desse jeito, so nao entendi uma coisa, onde tem  .ListColumns([myCol]) como abaixo, tentei colcocar no lugar do myCol , diretamente o myWeek.Value2 e eliminar a variavel myCol, mas nao aceitou, saberia por que?

    For Each myWeek In Range("semcad") 'semcad e o intervalo do cabecario do lado do cadastro
        myCol = myWeek.Value2
        .ListColumns([myCol]).DataBodyRange.Select

Tadao

Public Flag As Boolean
'==================================================================================================
'Procedimento para Mensagem de Prenchimento
Sub Desidir()
Dim myRange As Range, Resp1 As Integer, Resp2 As Integer
Flag = False
Call Desproteger

Resp1 = MsgBox("V O C E   D E S E J A   P R E E N C H E R ?" & vbCrLf & vbCrLf & _
        "Yes,todo Calendario ------------> Yes" & vbCrLf & _
        "No,so os Novos Registros ------> No" & vbCrLf & _
        "Cair Fora ------------------------> Cancel", vbYesNoCancel + vbQuestion + vbDefaultButton3)
Select Case True

    Case Resp1 = 6 'preenche tudo
    
        Resp2 = MsgBox("Tem Certeza?....Os Dados serao Irrecuperaveis!", _
                vbOKCancel + vbExclamation + vbDefaultButton2)
        If Resp2 = 1 Then
            Call PreencherTudo
            Flag = True
        Else
            Call Proteger
            Exit Sub
        End If
        
    Case Resp1 = 7 'preenche so os Yes
        Range("ShiHody[[Ctr]]").Select
        For Each myRange In Selection
            If myRange.Value = "Yes" Then
                Call Preencher1
                Flag = True
                Exit For
            End If
        Next
        If Flag = False Then MsgBox "Nao tem Dados a Serem Preenchidos"
        
    Case Resp1 = 2 'cancela
    
        Call Proteger
        Exit Sub
    
End Select

If Flag = True Then MsgBox "........Processado........."

Call Desproteger
Call Proteger

End Sub
'==============================================================================================
'Procedimento para preencher o calendario que esta com Yes
'Autor:Felipe Magno
'Alt.:Tadao Kano
Sub Preencher1()
Dim IntCal As String, IntCaDt As String, Campo As Range, Dia As Long
Dim Ci As Long, Li As Long
Dim pPlan As Worksheet
Dim CiTab As Long

Call Desproteger

Cd = Range("CsCd").Column      'Coluna com Domingo do Cadastro (Tabela ShiHody)
Cc = Range("CcCd").Column      'Coluna de Controle do Cadastro (Tabela ShiHody)
Ci = Range("CiCl").Column      'Coluna inicial do Calendario (Tabela ShiHody)
Cf = Range("CfCl").Column      'Coluna final do Calendario (Tabela ShiHody)
Li = Range("CiCl").Row         'Linha com os dias da semana do Calendario (Tabela ShiHody)

'seleciona as celulas com Yes
Flag = False
Call SelCel
'se nao tem Yes na coluna de controle sae fora
If Flag = False Then
    Range("CiCl").Select
    Application.EnableEvents = True
    Exit Sub
End If
Set pPlan = ActiveSheet
With pPlan

    'celula inicial da Tabela calendario
    CiTab = ActiveCell.Column

    For Each Campo In Selection
    
        If Campo.Column - CiTab + 1 <= 31 Then
        
            Dia = .Cells(Li, Campo.Column)
            If Dia > 0 Then
                Dia = Weekday(Dia)
                Campo.Value2 = .Cells(Campo.Row, Cd + Dia - 1)
            End If
        End If

        'muda a celula de controle de Yes para No
        If Campo.Column = Cf Then
            Cells(Campo.Row, Cc).Value2 = "No"
        End If
    Next
End With
Range("CiCl").Select

Call Proteger

End Sub
'==============================================================================================
'Procedimento que seleciona e limpa as linhas com Yes
Sub SelCel()
    Dim c As Range, Target As Range
    Dim Ctr As Range
    Application.ScreenUpdating = False

    Set Col = ActiveSheet.ListObjects(1).DataBodyRange.Columns
    'seleciona a coluna de Controle Yes/No
    Range("ShiHody[[Ctr]]").Select
    For Each c In Selection
        If c = "Yes" Then
            If Target Is Nothing Then
                Set Target = c
            Else
                Set Target = Union(Target, c)
            End If
        End If
    Next c
    If Not Target Is Nothing Then
        Target.EntireRow.Select
    Else
        Exit Sub
    End If
    
    'seleciona a interseccao das linhas com Yes e calendario de 1 a 31 dias
    Intersect(Selection, Range("ShiHody[[1]:[31]]")).Select
    'limpa as celulas selecionadas
    Selection.ClearContents
    Flag = True
    
End Sub
'==============================================================================================
'Procedimento para preencher tudo
Sub PreencherTudo()
Application.ScreenUpdating = False
Dim myWeek As Range, myCol As String
Call Desproteger
With ActiveSheet.ListObjects(1)
    Range("cal").ClearContents
    For Each myWeek In Range("semcad") 'semcad e o intervalo do cabecario do lado do cadastro
        myCol = myWeek.Value2
        .ListColumns([myCol]).DataBodyRange.Select
        Selection.Copy
        Call Selecao(myWeek)
        Selection.PasteSpecial xlPasteValues
    Next
    Application.CutCopyMode = False
    .ListColumns(["Ctr"]).DataBodyRange.Value2 = "No"
End With
Application.ScreenUpdating = True
Call Proteger
End Sub
'==============================================================================================
'Subrotina Selecao para selecionar as semanas do calendario
Sub Selecao(myWeek As Range)
Dim myRange As Range, Nomecoluna As String, Area As Range
With ActiveSheet.ListObjects(1)
    For Each myRange In Range("semcal") 'semcal e o intervalo acima do cabecario do lado do calendario
    If myRange.Value2 <> 0 Then
        'se for a mesma semana do cadastro faz uniao
        If Format(myRange, "ddd") = myWeek.Value2 Then
            Nomecoluna = myRange.Offset(1).Value2
            If Area Is Nothing Then
                Set Area = .ListColumns([Nomecoluna]).DataBodyRange
            Else
                Set Area = Union(Area, .ListColumns([Nomecoluna]).DataBodyRange)
            End If
        End If
    End If
    Next
    Area.Select
End With
End Sub

'==============================================================================================
'Procedimento para Desproteger,Desligar os Eventos e Mov Tela
Sub Desproteger()

Application.EnableEvents = False
ActiveSheet.Unprotect
Application.ScreenUpdating = False

End Sub
'==============================================================================================
'Procedimento para Proteger,Ligar os Eventos e Mov Tela
Sub Proteger()

Application.EnableEvents = True
ActiveSheet.Protect , DrawingObjects:=False
Application.ScreenUpdating = True

End Sub

Answer 34

Olá Tadao.

Desculpe a demora na resposta, estive muito ocupado...

"Se nao tiver nenhum comentario, acho que vou deixar desse jeito, so nao entendi uma coisa, onde tem  .ListColumns([myCol]) como abaixo, tentei colcocar no lugar do myCol , diretamente o myWeek.Value2 e eliminar a variavel myCol, mas nao aceitou, saberia por que?"

Você tentou como indiquei antes (me parece o mesmo problema):

"Tabela1[" & [Ret] & "]"

Dica: ao invés de usar

Case Resp1 = 6

faça:

Case Resp1 = vbYes

É muito mais intuitivo e fácil de analisar.

"Se nao tiver nenhum comentario, acho que vou deixar desse jeito"

É um pouco difícil analisar seu código sem executá-lo e sem ter uma visão mais clara do seu cenário, então fico a disposição caso tenha mais algum problema ou dúvida mais específica.

Grande abraço.

---

Filipe Magno

Answer 35

Obrigado pelas Dicas.

Aos poucos vou aprendendo as boas maneiras de programar.

O maior problema que sinto quando codifico, eh os nomes de intervalos que ja sao muitos. Nao sei uma boa maneira para gerenciar esses nomes.Sera que nao tem um atalho no editor VBA que faca aparecer todos os nomes de intervalos definidos? Na planilha, onde aparece o endereco da celula ativa, clicando no dropdown sai uma lista mas nao sae a descricao do nome, como sao muitos ate lembrar o nome codificado , ja queimou muitos fosforos... Dar um nome adequado tambem e outro problema. Depois que define um nome, e achar que nao ficou muito bom e quiser mudar, tambem e outro problema,nao? Comecei a fazer uma lista como abaixo numa planilha, mas no fim desisiti..... Tem uma boa forma de Gerenciar os Nomes?

Tadao

Nome das Areas	Localizacao	Descricao
CabSaidaHmMh	補助シート	Cabecalho de saida do filtro avancado para filtro do VBA de Homens e Mulheres juntos
CabSaidaHm	補助シート	Cabecalho de saida do filtro avancado para filtro do VBA de Homens
CabSaidaMh	補助シート	Cabecalho de saida do filtro avancado para filtro do VBA de Mulheres
CalHmMh	カレンダー	Area do calendario usado para formatar as cores das horas Homens e Mulheres
CalHom	カレンダー	Area do calendario usado para VBA quando der dois clicks mudar para 休み na area dos Homens
CalMul	カレンダー	Area do calendario usado para VBA quando der dois clicks mudar para 休み na area das Mulheres
DataBase	カレンダー	Area dos nomes de Homens e Mulheres juntos por hora de trabalho ao lado do calendario que vai servir de base para filtrar e tirar as celulas vazias
DataBaseH	カレンダー	Area dos nomes de Homens por hora de trabalho ao lado do calendario que vai servir de base para filtrar e tirar as celulas vazias
DataBaseM	カレンダー	Area dos nomes de Mulheres por hora de trabalho ao lado do calendario que vai servir de base para filtrar e tirar as celulas vazias
DataBaseCab1	カレンダー	Nome da primeira celula do cabecalho de areas usado em VBA para mudar o valor e obter nomes de horarios diferentes dos Homens/Mulheres e Homens
DataBaseCab2	カレンダー	Nome da primeira celula do cabecalho de areas usado em VBA para mudar o valor e obter nomes de horarios diferentes das Mulheres
NomeHomem	カレンダー	Area onde estao os nomes masculinos no calendario, foi usado para formatar as cores das letras(formatacao condicional)
NomeMulher	カレンダー	Area onde estao os nomes feminimos no calendario, foi usado para formatar as cores das letras(formatacao condicional)
SougeiHm	送迎	Inicio do cabecalho do sougei homem
SougeiMh	送迎	Inicio do cabecalho do sougei mulher
Saida21	補助シート	Area de saida de 21horas homem mulher
Saida22	補助シート	Area de saida de 22horas homem mulher
Saida24	補助シート	Area de saida de 24horas homem mulher
Saida21_22	補助シート	Area de saida de 21+22horas homem mulher
Saida21_22_24	補助シート	Area de saida de 21+22+24horas homem mulher
Criterio21	補助シート	Area de criterio de 21horas homem mulher
Criterio22	補助シート	Area de criterio de 22horas homem mulher
Criterio24	補助シート	Area de criterio de 24horas homem mulher
Criterio21_22	補助シート	Area de criterio de 21+22horas homem mulher
Criterio21_22_24	補助シート	Area de criterio de 21+22+24horas homem mulher
SaidaH21	補助シート	Area de saida de 21horas homem
SaidaH22	補助シート	Area de saida de 22horas homem
SaidaH24	補助シート	Area de saida de 24horas homem
SaidaH21_22	補助シート	Area de saida de 21+22horas homem
SaidaH21_22_24	補助シート	Area de saida de 21+22+24horas homem
CriterioH21	補助シート	Area de criterio de 21horas homem
CriterioH22	補助シート	Area de criterio de 22horas homem
CriterioH24	補助シート	Area de criterio de 24horas homem
CriterioH21_22	補助シート	Area de criterio de 21+22horas homem
CriterioH21_22_24	補助シート	Area de criterio de 21+22+24horas homem
SaidaM21	補助シート	Area de saida de 21horas mulher
SaidaM22	補助シート	Area de saida de 22horas mulher
SaidaM24	補助シート	Area de saida de 24horas mulher
SaidaM21_22	補助シート	Area de saida de 21+22horas mulher
SaidaM21_22_24	補助シート	Area de saida de 21+22+24horas mulher
CriterioM21	補助シート	Area de criterio de 21horas mulher
CriterioM22	補助シート	Area de criterio de 22horas mulher
CriterioM24	補助シート	Area de criterio de 24horas mulher
CriterioM21_22	補助シート	Area de criterio de 21+22horas mulher
CriterioM21_22_24	補助シート	Area de criterio de 21+22+24horas mulher
テーブル1	男子名簿	Area do cadastro dos homens
テーブル2	女子名簿	Area do cadastro das mulheres
テーブル3	補助シート	Area dos tamanhos dos uniformes
テーブル4	補助シート	Area onde estao os 通勤
テーブル5	補助シート	Area onde tem os codigos das horas e suas descricoes
DataBaseHrNome	カレンダー	Area dos nomes de Homens e Mulheres com hora de trabalho no  inicio ao lado do calendario que vai servir de base para filtrar e tirar as celulas vazias
Ws21_Data	_21配置	Area da Data do Cabecario
Ws22_Data	_22配置	Area da Data do Cabecario
Ws24_Data	_24配置	Area da Data do Cabecario
Wssg_Data	送迎	Area da Data do Cabecario
Wskb_Data	掲示板	Area da Data do Cabecario
Nome Resumido	Objeto	Descricao
W	ThisWorkbook.ActiveSheet
Wsc	Worksheets("カレンダー")
Wssg	Worksheets("送迎")
Wskb	Worksheets("掲示板")
Wsax	Worksheets("補助シート")
we	Worksheets("メール")
wMen	Worksheets("メール内容")
Ws21	Worksheets(_21配置")
Ws22	Worksheets(_22配置")
Ws24	Worksheets(_24配置")

Answer 36

Olá!

"Obrigado pelas Dicas.

Aos poucos vou aprendendo as boas maneiras de programar."

Que bom, fico feliz. Também aprendo muito todos os dias e a melhor forma é praticando, sem dúvida!

"Sera que nao tem um atalho no editor VBA que faca aparecer todos os nomes de intervalos definidos? Na planilha, onde aparece o endereco da celula ativa, clicando no dropdown sai uma lista mas nao sae a descricao do nome, como sao muitos ate lembrar o nome codificado , ja queimou muitos fosforos... "

Existe uma forma nativa de gerenciá-los diretamente pelo Excel. E acredite, é muito fácil exibir a descrição justamente no local que você indicou! Basta ir no "Gerenciador de Nomes" da "Guia Fórmulas" (ou atalho: Ctrl+F3) e editar o campo comentários, como na imagem abaixo:

Simples assim!

"Depois que define um nome, e achar que nao ficou muito bom e quiser mudar, tambem e outro problema,nao?"

Lembra de um exemplo que te mandei que havia uma constante chamada "Criterios0"? Ela é justamente para isso. Quando tenho que utilizar intervalos nomeados no código crio um módulo chamado "mPublico" (todos os meus projetos possui esse módulo) e declaro nele, entre outras coisas, todos os intervalos nomeados como constantes globais. Assim, se precisar trocar o nome de algum intervalo basta ir nesse módulo e alterar a atribuição de sua constante. Assim uma única troca será necessária. Bem simples!

"Tem uma boa forma de Gerenciar os Nomes?"

O Excel tem uma ferramenta (além do "Gerenciador de Nomes") que permite exibir os nomes definidos:

Basta ir em "Fórmulas >> Usar em Fórmulas >> Colar Nomes... >> Colar Lista.

Será inserida na planilha uma lista de 2 colunas com todos os nomes definidos:

A desvantagem desta opção é que, ao contrário do "Gerenciador de Nomes", os comentários não são exibidos.

Abraço.

---

Filipe Magno

Answer 37

Ola Felipe, obrigado pela inumeras dicas.

Hoje uso dois monitores, uma deixo a planilha aberta e o outro, um monitor vertical, para os codigos. Esta bom de ter um terceiro monitor para deixar os nomes de intervalos aberto, nao?.

Pelo que entendi sobre o Criterios0, voce renomeia os intervalos e usa o novo nome, nao e?. Assim, se mudar o nome do intervalo na planilha, mudaria so no modulo dito "mPublico", certo?,.....mas ai eu teria outro problema, ja e um trabalho dar um nome codificado facil de memorizar para os intervalos, e assim feito teria que dar outro apelido para os intervalos que serao utilizados nas codificacoes, aumentando a lista de nomes,nao?.....mas talvez, assim seria mais facil de procurar o nome, que e so acessar o modulo onde esta definido o apelido dos intervalos,nao?....vou experimentar a nova maneira.Obrigado.

Ultimamente aprendi um outro atalho que e Shift+F2 e Ctrl+Shift+F2 que vai e volta na subrotina Call que esqueceu em que modulo foi codificado.

Essa ultima dica do Colar Lista tambem e muito bom para deixar documentados,nao?.Obrigado mais uma vez.

Tadao

Answer 38

"Assim, se mudar o nome do intervalo na planilha, mudaria so no modulo dito "mPublico", certo?,.....mas ai eu teria outro problema, ja e um trabalho dar um nome codificado facil de memorizar para os intervalos, e assim feito teria que dar outro apelido para os intervalos que serao utilizados nas codificacoes, aumentando a lista de nomes,nao?."

Na verdade não há outro problema: normalmente utilizo para a constante o mesmo nome dado ao intervalo, de forma que somente ficarão diferentes se for necessário alterar o nome do intervalo (outra opção seria utilizar um prefixo padrão para todas as constantes, como por exemplo "cnsVariavel", assim ao digitar "cns" + "Crtl+Espaço" você teria a lista de todas as constantes disponíveis). Mesmo assim, caso queira repadronizar, basta um localizar e substituir em todo o projeto e tudo está resolvido. Além disso tem as seguintes vantagens:

• O Shift+F2 funciona com variáveis, indo direto para o dito módulo. Ctrl+Shift+F2 retorna ao ponto anterior.
  
• Ctrl+I exibe um dropdown com o valor da constante.
• Pode-se inserir comentários para cada constante criada, facilitando sua identificação.

Abraço.

---

Filipe Magno

Answer 39

Ohhh Felipe, custei a entender o que voce disse.

Essa tecnica e muito boa,nao?. Deixo renomeado como constantes publicas os intervalos com cns iniciais no modulo mPublico, e no modulo onde vou fazer as rotinas apos digitar cns + Ctrl+Espaco, sae a lista, seleciona e Enter....se quiser ver o comentario da constante, da dois cliques encima da constante e Shift+F2 que vai para o modulo mPublico, depois e so Ctrl+Shift+F2 para retornar....muito bom, Obrigado.

Esse Ctrl +l (acho que deve ser "ele" e nao "i" nao funcionou, sea que e Ctrl +i ?. Se deixar selecionado a constante e der um Ctrl+i, aparece o conteudo da constante, nas o dropdown das constantes com seus valores.

Tadao

Answer 40

"Se deixar selecionado a constante e der um Ctrl+i, aparece o conteudo da constante,"

Isso, me confundi na hora de escrever. Quiz dizer que aparece o valor da constante.

"se quiser ver o comentario da constante, da dois cliques encima da constante e Shift+F2"

Não precisa selecionar, basta o cursor estar sobre a palavra, em qualquer ponto (vale para funções também).

Abraço.

---

Filipe Magno

Answer 41

Ohh..Felipe, agora entendi o que voce quiz dizer,.....descupe, estava interpretando errado a sua explanacao.

Agora fica mais facil de codificar e procurar os valores, muito Obrigado e desculpe pelas insistencias.

Tadao

Answer 42

Que bom que as respostas estão lhe ajudando, fico feliz.

Se tiver mais dúvidas fique a vontade. Às vezes demoro um pouco para responder, mas é só falta de tempo mesmo.

Abraço.

---

Filipe Magno

Answer 43

Ola Felipe, bom dia.

Em primeiro lugar, obrigado pelas varias dicas que tem proposto.Estou aproveitando muito bem, as suas sugestoes.

Numa das dicas que voce passou, de selecionar uma coluna da Tabela com Variavel, funciona muito bem, e fui aproveitar para esconder a coluna indicada na variavel substituindo o Select por Hidden=True, mas nao funcionou.Precisei de colocar antes um EntireColumn senao nao funcionava, mas nas varias tentativas alumas formas de codificar aceita sem o EntireColumn, porque sera?isso deve ser uma característica interna. do VBA, como voce explicou uma vez,ou algum erro meu?

Abaixo a dica qu voce passou...

Range("Tabela1[" & [NomeVariavel] & "]").Select

Abaixo, resultado das varias tentativas  numa Tabela que tem como nome do Cabecario AHomem,BHomem,CHomem,DHomem,AMulher,BMulher,CMulher,DMulher....

Dim myrange As Range, colecao As Range, nome1 As String, nome2 As String, nome3 As String, Tab1 As ListObject
Sub teste3()
nome1 = "AMulher"
nome2 = "CMulher"
nome3 = "AMulher:CMulher"

Set Tab1 = ActiveSheet.ListObjects(1)

Tab1.Range.Columns(3).Select 'ok
Tab1.Range.Columns(3).Hidden = True 'ok

Tab1.ListColumns(4).Range.Select 'ok
Tab1.ListColumns(4).Range.Hidden = True 'erro
Tab1.ListColumns(4).Range.EntireColumn.Hidden = True 'ok

Tab1.Range.Columns("a").Select 'ok
Tab1.Range.Columns("a").Hidden = True 'ok
Tab1.Range.Columns("a:c").Select 'ok
Tab1.Range.Columns("a:c").Hidden = True 'Select 'ok

Tab1.Range.Columns("3:4").Select 'erro
Tab1.ListColumns("4:5").Range.Select 'erro
Tab1.ListColumns("c").Range.Select 'erro
Tab1.ListColumns("a:c").Range.Select 'erro


Tab1.Range.Columns(nome1).Select 'erro
Tab1.ListColumns(nome1).Range.Select 'ok
Tab1.ListColumns(nome1).Range.Hidden = True 'erro
Tab1.ListColumns(nome1).Range.EntireColumn.Hidden = True 'ok

Tab1.ListColumns(nome3).Range.Select 'erro

Tab1.ListColumns(nome1 & ":" & nome2).Range.Select 'erro

Range("Tabela1[[" & [nome1] & "]:[" & [nome2] & "]]").Select 'ok
Range("Tabela1[[" & [nome1] & "]:[" & [nome2] & "]]").Hidden = True 'erro
Range("Tabela1[[" & [nome1] & "]:[" & [nome2] & "]]").EntireColumn.Hidden = True 'ok

 End Sub

Mas o objetivo do teste, era que eu precisava esconder as colunas que por ,por exemplo incia com "A", ou seja "A*", que escondesse as colunas AHomem e AMulher.

Nao teria uma forma de usar o asteristico para selecionar as colunas da Tabela?

Tadao

Answer 44

Olá Tadao.

Fico feliz que as dicas estão sendo úteis.

"isso deve ser uma característica interna. do VBA, como voce explicou uma vez,ou algum erro meu?"

Note que há um padrão bastante simples: você somente pode ocultar Linhas ou Colunas Inteiras, logo não há como ocultar uma coluna quando o resultado do intervalo se refere apenas à tabela e não há planilha.

Veja, toda a coluna selecionada:

Tab1.Range.Columns(3).Hidden = True 'ok

apenas a coluna da Tabela selecionada:

Tab1.ListColumns(4).Range.Hidden = True 'erro

Sobre a sintaxe, como em:

Tab1.ListColumns("c").Range.Select 'erro

perceba que é o mesmo das respostas anteriores, este não é um formato válido para seleção em Tabelas.

"Mas o objetivo do teste, era que eu precisava esconder as colunas que por ,por exemplo incia com "A", ou seja "A*", que escondesse as colunas AHomem e AMulher.

Nao teria uma forma de usar o asteristico para selecionar as colunas da Tabela?"

Diretamente acho que não. Mas basta você fazer um "For" em todas as colunas (cabeçalho) e testar o nome da coluna.

Abraço.

---

Filipe Magno

Answer 45

Obrigado pelo pronto retorno.

Entao, o jeito e usar o For Next ou For Each, entendido.

Outra coisa que gostaria de perguntar, e sobre as rotinas que estou fazendo nos Ficheiros do propio Ficheiro de dados. Se mudar a rotina e enviar pro usuario, como o ficheiro que tenho que esta com dados antigos, ele teria que atualizar tudo. Sera que e melhor sempre fazer um ficheiro so de rotinas que acessam outros ficheiros de dados?

Tadao

Answer 46

Boa noite Tadao.

Desculpe a demora. Precisei me ausentar devido a um problema de saúde em casa.

"Sera que e melhor sempre fazer um ficheiro so de rotinas que acessam outros ficheiros de dados?"

Isto depende muito, na minha opinião. Eu tenho muitos projetos das duas formas, mas na maior parte deles eu sou o usuário principal, então customizo da forma que acho mais eficiente. Fazer em arquivos separados pode dar maior flexibilidade para atualizações, mas pode deixar o processo mais "burocrático" para o usuário, forçando-o a utilizar sempre mais de um arquivo para realizar uma dada tarefa (principalmente para tarefas simples). Mas isso depende muito das características dos projetos. De uma forma geral, manter uma base de dados para registros puros e outras pastas de trabalho contendo as ferramentas para analisar os dados apenas é uma boa prática.

Também ressalto que enviar atualizações para o usuário e "forçá-lo" a mesclá-las manualmente em outra base é algo praticamente insustentável, já que é quase certo que em algum momento o processo irá falhar, seja na inabilidade do usuário ou devido a alguma alteração na pasta de trabalho em uso que não foi prevista.

Abraço.

---

Filipe Magno

Answer 47

Obrigado pelas observacoes e desculpe a demora do retorno.

Vou tentar montar um sistema como da figura abaixo da proxima vez.

Tadao