Consolidar planilhas resultando de linha e coluna com result

Flavio Guedes

03-10-2013 20:54:56

Olá a todos!

Gostaria de pedir sua ajuda de vocês em uma planilha do Excel 2007/2010, que consta em anexo.

Vou descrever o funcionamento inicial da planilha (das planilhas, pois estou tentando unificar duas em uma), e depois o que tive de modificar (ou tentar):

É uma planilha para controle de distribuição de ativos (desktops e notebooks), nela criei um controle de entrada e saída de ativos em uma aba e todas as entradas que insiro, de acordo com o modelo de desktop que consta na coluna, na aba “distribuição de ativos” a quantidade fica disponível como estoque (as formulas nessa parte são simples) e faço a distribuição atribuindo uma letra de acordo com o modelo que um usuário está recebendo ou trocando com outro usuário que necessita de um desktop melhor. O modelo fica na linha 9 da planilha “distribuição de ativos” e a coluna que indica o usuário que receberá aquele modelo fica na coluna C.

Criei uma formula CONT.SE nas distribuições atribuindo letras, U para desktop em uso que faz com que saia do estoque e vá para item em uso, na linha 4 dessa planilha, P para processo de baixa (linha 5) e D para descarte (item descartado ou lixo, linha 6), até aí tudo bem.

O problema começa quando recebi uma planilha de inventário de ativos (colei em uma nova aba chamada “inventário”) da minha unidade, onde constavam o detalhamento de todos os ativos e eu precisava vincular uma a outra de forma automática. Sendo assim comecei a tentar isso, primeiro a coluna da planilha de distribuição onde constam os usuários (coluna C) eu criei um link com a coluna “usuário” dessa planilha de inventário para que todo nome inserido lá venha para a planilha “distribuição de ativos”.

Criei um lista suspensa (que já existia para planilha “distribuição de ativos”) para essa planilha “inventário” para a coluna “situação CPU”, que é a mesma U, P e D (Uso, Processo de baixa e Descarte).

E adicionei todos os modelos dos desktops que constam na planilha “distribuição de ativos”(linha 9) à planilha “inventário”, na coluna “modelo de CPU” (coluna K) para terem os mesmos dados em ambas as planilhas.

O que eu gostaria é o seguinte, exemplificando:

Antes da planilha “inventário” eu selecionava uma letra (U, P ou D) na planilha “distribuição de ativos” para começar a distribuição, agora eu preciso distribuir pela planilha “inventário” e os dados inseridos lá, de acordo com o nome do usuário (coluna T), o modelo que ele tem (coluna K) e a situação da CPU (coluna A) esses dados se distribuam na planilha “distribuição de ativos” em concordância, ou seja, usuário em usuário, situação em situação e modelo de acordo com o modelo, tudo conforme inserido na planilha “inventário”.

O problema é que não sei como fazer, pois na planilha “distribuição”, que não alteraria mais, só vai receber dados da planilha “inventário”, e tenho que criar link entre esses itens de forma que a coluna "usuário" e a linha 9 da planilha "distribuição de ativos combinando resulte na entrada que eu der de acordo com U, P ou D.

Espero que tenha entendido minha explicação e mais ainda que você possa me ajudar, e desculpa tantas palavras, realmente precisei explicar detalhadamente para que vocês entendam.

Desde já te agradeço a você Tomas que de pronto respondeu meu email e a todos, estou realmente desesperado pois preciso desse controle.

Um abraço.

Flavio Guedes

03-10-2013 20:56:25

É pessoal,

Não consegui anexar a planilha aqui, extensão .xlsx diz aque não é permitida.

Help pessoal?? Sem a planilha ninguem vai entender nada!!

joseA

03-10-2013 23:09:29

Basta compactar a planilha. ;)

Flavio Guedes

04-10-2013 22:40:36

Ops, verdade!!!

Está anexado no primeiro post e nesse tambem, se alguem puder ajudar!!!!

Obrigado.

Flavio Guedes

08-10-2013 13:51:18

E aí galera,

Estão conseguindo se encontrar na planilha, "levei uma surra" e realmente não consegui, estou na esperança que vocês consigam!!! :roll:

Herbert

11-10-2013 08:04:52

Flavio, Bom Dia!

Eu criei uma Rotina através de um módulo no VBA que preenche automaticamente os campos com a Situação, levando em conta Usuário X Modelo X Situação.

Acrescentei uma linha oculta abaixo na linha do Modelo na Sheet de "Distribuição de Ativos" para obter a coluna de preenchimento correta.

O nome da macro é "preencherAtivos", dá uma validada pra ver se é isso que você necessita.

OBS: Na Sheet "Inventário" eu criei 3 colunas no final para organizar os valores a serem utilizados, só que deixei uma formula ARRUMAR em Modelo, retire ela para não dar pau na hora de procurar o valor, pois se tiver algum modelo com mais de 1 espaço, ele vai dar erro.


Qualquer dúvida estou a disposição.


Att,

Herbert Souza

Flavio Guedes

11-10-2013 13:45:37

Bom dia Herbert!!

Antes de mais nada gostaria de te agradecer pela ajuda, estou tentando "botar esse treco" pra funcionar, retirei a formula ARRUMAR como você pediu, tentei habilitar a macro mas está dando algum erro:

colunaModelo = Application.WorksheetFunction.IfError(Application.WorksheetFunction.HLookup( _
strModelo, Sheets("Distribuição de Ativos").Range("E9:AD10"), 2, 0), "")

CORRIGINDO: Eu retirei a fórmula ARRUMAR da coluna Situação e Modelo, por isso deu erro, mas já corrigi :?

Duas questões:

Fazendo alguns testes, simulando uma troca de Modelo de CPU, quando eu rodo a macro, em Distribuição de Ativos a troca funciona mas não some o ultimo modelo usado, se eu trocar por outro modelo de cpu novamente os tres modelos usados permanecem quando deveria ficar apenas um.

Tambem, existe a possibilidade da macro rodar automaticamente ao mudar os dados??

Obrigado pelo esforço e ajuda!!

Flavio Guedes.

Herbert

11-10-2013 17:23:23

Flavio,

Vamos lá:

1º Ela não apaga e mantém os dados anteriores porque eu fiz um exemplo de preenchimento apenas, mas se você quiser que ela apague você pode criar uma Sub de limpeza e chama-lá no após inicializar as variáveis na Sub de preencherAtivos.

2º Se você quiser que ela atualize sozinha, chame a Sub preencherAtivos no evento Workbook_Open da Pasta de Trabalho, assim toda vez que a pasta for inicializada a macro será chamada automaticamente, lembrando que a pasta não estará salva, pois houve alterações, mas você pode ajustar isso também na macro, incluindo uma linha de código: Workbook.save

Exemplo: (Só adiciona-lo antes da Sub de preencherAtivos)

Private Sub limparDados()

Sheets("Distribuição de Ativos").Select
Range("E12:AD200").ClearContents

End Sub

Qualquer Dúvida estou a disposição.


Att

Herbert Souza

Flavio Guedes

12-10-2013 17:27:25

Fala Herbert.

Tentei fazer como você falou mas continua do mesmo jeito, dá uma olhada abaixo e vê se é assim mesmo:

------------------------
Private strUsuario, strSituacao, strModelo As String
Private linhaUsuario, quantidadeUsuarios As Integer
Private colunaModelo As Integer

Private Sub inicializarVariaveis()

strUsuario = ""
strSituacao = ""
strModelo = ""
linhaUsuario = 12
quantidadeUsuarios = Application.WorksheetFunction.CountA(Sheets("Distribuição de Ativos").Range("C11:C1000"), ">0")

End Sub

Private Sub limparDados()

Sheets("Distribuição de Ativos").Select
Range("E12:AD200").ClearContents

End Sub
Public Sub preencherAtivos()

inicializarVariaveis

Dim auxiliar As Integer

For auxiliar = 1 To quantidadeUsuarios Step 1

' Variavel que recebe o nome do Usuario Atual
strUsuario = Sheets("Distribuição de Ativos").Cells(linhaUsuario, 3)

If strUsuario = "" Or IsNull(strUsuario) Or strUsuario = 0 Then

Else

' PROCV que recebe a Situação
strSituacao = Application.WorksheetFunction.IfError( _
Application.WorksheetFunction.VLookup(strUsuario, Sheets("Inventário").Range("AG21:AI1000"), 2, 0), "")

' PROCV que recebe a Modelo
strModelo = Application.WorksheetFunction.IfError( _
Application.WorksheetFunction.VLookup(strUsuario, Sheets("Inventário").Range("AG21:AI1000"), 3, 0), "")

End If

If strSituacao = "" Or IsNull(strSituacao) Then

Else

' Procura o Modelo respectivo ao Usuario através de um PROCH e retorna o Número da Coluna que deve ser preenchido
colunaModelo = Application.WorksheetFunction.IfError(Application.WorksheetFunction.HLookup( _
strModelo, Sheets("Distribuição de Ativos").Range("E9:AD10"), 2, 0), "")

Cells(linhaUsuario, colunaModelo) = strSituacao

End If

linhaUsuario = linhaUsuario + 1

Next

End Sub
---------------------------------------------

Continuo te agradecendo pela força, mas realmente sou ruim com macro e sem você pra auxiliar nao vai!!!

Um abraço.

Flavio Guedes.

Herbert

14-10-2013 03:56:27

Flavio, Bom Dia!

Vamos lá...

Vi que você adicionou o procedimento de limpeza no local correto, então só falta chama-lo na Sub preencherAtivos.

Exemplo:

Public Sub preencherAtivos()

inicializarVariaveis


Chama a função de limparDados AQUI que nem fiz para chamar a função inicializarVariaveis acima. (Somente o Nome do Procedimento)


... Continuação do Código ...


Assim, sempre que inicializar o procedimento de preenchimento de ativos ele irá limpar o intervalo que indiquei na Sub de limpeza e executar o preenchimento posteriormente.

Dúvidas estou a disposição.

Att,

Herbert Souza

Flavio Guedes

18-10-2013 15:24:46

Fala Herbert, desculpe a demora!

Cara, até entendi o que você pediu pra eu fazer mas só não sei fazer :?

Tem como você adicionar o código completo a partir daquele que eu te enviei ou me dar um passo a passo?

Te agradeço muito.

Um abraço.

Flavio Guedes.

Herbert

21-10-2013 02:32:02

Fala Flávio,

Verifica se este modelo em anexo já atende as suas necessidades... ele atualiza automaticamente e salva a pasta sempre que a mesma é inicializada e adicionei um botão de atualização manual só para efeito de teste, se quiser retirá-lo, sinta-se a vontade.

Abraço!

Qualquer dúvida estou a disposição.


Att,

Herbert Souza