Função VLookUP

Visual Basic for Aplication e macros no Excel.
Regras do fórum
Sua dúvida foi respondida? Marque como RESOLVIDO em seus tópicos, usando o botão com marca verde. Imagem

Re: Função VLookUP

Mensagempor Mauro Coutinho » Qui Jan 11, 2018 8:28 pm

menezes, peço a gentileza de ler as regras do Forum, onde diz que devemos ter paciencia e aguardar algum retorno sem ficar cobrando pelo mesmo, uma vez que toda a ajuda aqui é voluntária e depende da disposição de tempo de cada colaborador.

Geralmente, quando uma questão é mais simples e está bem detalhada as respostas são mais rápidas, agora quanto a sua postagem, eu procurei analisar várias vezes e confesso está um pouco confusa, e antes que continuemos com suposições, por gentileza detalhe melhor o que pretende, pois no inicio da postagem você colocou uma rotina e depois outra diferente onde temos alguns erros como por exemplo : você define a Variável "Referencia" como Range e depois utiliza a mesma em uma chamada como se fosse o nome de um Range "Range("Referencia"", ainda temos "Tabela" como Range e na planilha temos o mesmo nome em um Range Nomeado.

De inicio eu entendi que quer procurar o valor de cada celula selecionado na linha 2 no range "Range("B2:O2")" e está utilizando o Evento "SelectionChange", mas não compreendi porque temos formula na linha 3, outra coisa, nos ranges pesquisados na "Planilha4" temos vários valores repetidos e o procv trará sempre o primeiro encontrado, alem de que se um valor não existir teremos erros, e assim temos de trata-los e saber o que é para ir para a celula.
Então, por gentileza, detalhe melhor sua necessidade, quanto mais informações tivermos ficará mais simples ajudar sem ter de ficar em suposições.

Veja que pela primeira rotina que postou inteira, o correto seria como está abaixo, mas como disse acima, teremos erros devido aos valores pesquisados não existirem.
Código: Selecionar todos
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim i, j As Integer
Dim TabelaFonte As Range

i = Target.Column

    With Sheets("Planilha4")
        Set TabelaFonte = .Range(.Cells(3, 122 + j - 1), .Cells(2649, 148))
    End With

    If Not Intersect(Target, Range("B2:O2")) Is Nothing Then
                             
        ' Preenche os valores das células subsequentes ao Target com os valores da primeira linha da TabelaFonte
        For j = i + 1 To 14
           
            ActiveCell.Value = Application.WorksheetFunction.VLookup(Cells(2, j - 1).Value, TabelaFonte, 2, False)
               
            'Congelamos a rotina para evitar de ficar reiniciando cada vez que uma celula é selecionada
            Application.EnableEvents = False
            Cells(2, j).Select
       
        Next
       
        'Descongelamos para continuar no FOR
        Application.EnableEvents = True
   
    End If

End Sub


[]s
Mauro Coutinho - Moderador
São José dos Pinhais-Pr


Se a resposta foi últil para você, por gentileza, clique na mãozinha ao lado direito da sua tela. No canto superior.
Avatar do usuário
Mauro Coutinho
Ninja do Excel
Ninja do Excel
 
Mensagens: 3934
Registrado em: Sex Mar 26, 2010 9:24 pm
Localização: São José dos Pinhais - Pr
Has thanked: 181 times
Have thanks: 1447 times

{ SO_SELECT }

Re: Função VLookUP

Mensagempor menezes7 » Sex Jan 12, 2018 8:56 am

O arquivo tem duas planilhas:

"Planilha1" - Possui uma tabela onde o usuário deve preencher as células do intervalo B2:O2. As opções de preenchimento de cada célula são fornecidas por uma lista de validação de dados que varia conforme o valor selecionado na célula anterior. Em outras palavras, as células do intervalo são interdependentes. Assim, para cada valor escolhido na célula B2, há uma lista de opções diferente para ser escolhida na célula C2. Por conseguinte, o valor que seleciono na célula C2 determina uma opção de valores para ser escolhido na célula D2 e assim sucessivamente.

O problema ocorre quando, após preencher todas as células do intervalo B2:O2, eu decido alterar o valor de qualquer uma dessas células. Nesse caso, terei que alterar manualmente o valor das células subsequentes. Gostaria de uma macro que preenchesse automaticamente o valor das células subsequentes com uma "sugestão" de valor a partir do valor escolhido na célula anterior.

Em outras palavras, para cada alteração em Cells(2,i), gostaria que o valor de Cells(2,j) fosse preenchido automaticamente com uma "sugestão" de valor, dado que i < j <= 14.

Essa "sugestão" o excel encontraria na Sheets("Planilha4").Range("DQ3:ER2649"), que é a "TabelaFonte" onde estão listados todos os valores possíveis de serem selecionados no intervalor "B2:O2".

No código que apresentei, eu tento preencher automaticamente o valor de Cells(2,j) com um procv, a partir do valor preenchido na célula imediatamente anterior Cells(2,j-1). Nesse caso o intervalo da "TabelaFonte" tem que variar para o procv funcionar.

Exemplo: se eu altero o valor da célula B2, a célula C2 vai procurar o valor de B2 no intervalo "Planilha4!DQ3:ER2649". O valor da célula D2, por conseguinte, vai procurar o valor de C2 no intervalo "Planilha4!DR3:ER2649", e assim sucessivamente, até a célula O2 (coluna j=14).

Espero que agora tenha ficado mais claro

Código: Selecionar todos
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim i, j As Integer
Dim TabelaFonte, Tabela, Referencia As Range

i = Target.Column

Set Tabela = Sheets("Planilha4").Range("DQ3:ER2649")
Set Referencia = Range(Tabela).Find(Target.Value, , , xlWhole)
Set TabelaFonte = Sheets("Planilha4").Range("Referencia", Cells(2649, 148))

If Not Intersect(Target, Range("B2:O2")) Is Nothing Then
                       
    For j = i + 1 To 14
    Cells(2, j).Select
    ActiveCell.Value = Application.WorksheetFunction.VLookup(Cells(2, j - 1).Value, TabelaFonte, 2, False)
    Next

End If

End Sub
menezes7
Membro
Membro
 
Mensagens: 11
Registrado em: Seg Jan 08, 2018 4:13 pm
Has thanked: 0 time
Have thanks: 0 time

Re: Função VLookUP

Mensagempor Mauro Coutinho » Sex Jan 12, 2018 10:53 am

menezes, vamos ver se entendi, porque pra mim ainda está um pouco confuso :

Primeiro, no modelo que enviou temos Validação dos Ranges "C2 até O2", B2 não tem.
Depois diz em executar a rotina quando efetuar alteração, mas está utilizando uma rotina no Evento "Worksheet_SelectionChange", ou seja, a rotina será executada sempre que selecionar qualquer celula, e mesmo que tenha uma no Evento "Worksheet_Change" o Evento "SelectionChange" é executado primeiro.

Pela sua explicação, estou entendo o seguinte:
Altero o Valor em B2, e atraves do PROCV localizamos este valor na "TabelaFonte" e preenchemos C2 com este valor; e na sequencia, devemos pesquisar o valor que foi preenchido em C2, e assim por diante, ou seja :

B2 => C2 = resultado referente PROCV Valor de B2
C2 => D2 = resultado referente PROCV Valor de C2
D2 => E2 = resultado referente PROCV Valor de D2
................
N2 => O2 = resultado referente PROCV Valor de N2
O2 => ??? = resultado referente PROCV Valor de O2 - ???

e assim por diante até O2, só que em O2 devemos para a pesquisa pois não teremos lançamento em "P2".
Todas as ações acima são executadas automaticamente, pois estamos selecionando a próxima celula com a instrução "Cells(2, j).Select", dai o porque estar no evento "SelectionChange", só que como citei acima, este evento ocorre primeiro, assim sendo, se apos tudo preenchido eu quiser alterar o valor de "D2", ao selecionar o mesmo, teremos toda a execução novamente do evento "SelectionChange" e ao alterar a celula, a execução do evento "Worksheet_Change", só que se teclar Enter ou passar para a próxima, estaremos executando novamente toda a rotina.
Veja na rotina inicial, é definido TabelaFonte no "Range(Cells(3, 119 + j - 1)", só que a coluna DQ3 é 121, por isto alterei, outra coisa, quando usei a Propriedade EnableEvents e alterei a ordem da linha "Cells(2, j).Select", é para evitar de ficar reiniciando do inicio e com isto estar zerando o Loop.
Uma outra coisa, quando diz : "Por conseguinte, o valor que seleciono na célula C2 determina uma opção de valores para ser escolhido na célula D2 e assim sucessivamente.", esta opção de valores está se referindo a Validação ? veja que dependendo, se tivermos algum valor diferente teremos erro devido a validação da celula.
Mauro Coutinho - Moderador
São José dos Pinhais-Pr


Se a resposta foi últil para você, por gentileza, clique na mãozinha ao lado direito da sua tela. No canto superior.
Avatar do usuário
Mauro Coutinho
Ninja do Excel
Ninja do Excel
 
Mensagens: 3934
Registrado em: Sex Mar 26, 2010 9:24 pm
Localização: São José dos Pinhais - Pr
Has thanked: 181 times
Have thanks: 1447 times

Re: Função VLookUP

Mensagempor menezes7 » Sex Jan 12, 2018 1:38 pm

1 - Pode considerar a partir de C2
2 - Só conheço o evento Worksheet_Change... se houver algum evento relacionado apenas a alteração no valor das células, peço que a considere.
3 - Quando eu altero o valor em B2, um procv deve buscar esse valor na TabelaFonte e retornar o primeiro valor correspondente na coluna à direita (ou seja, o procv deve buscar o valor correspondente na coluna de índice 2). Só que, conforme o valor em C2 é preenchido, um novo procv deve buscar o valor em C2 e retornar na célula D2 o valor correspondente na coluna de índice 2 da TabelaFonte, sendo que a própria referência ta TabelaFonte, nesse caso, irá variar, pois não conseguirei buscar o valor de C2 em uma tabela fonte cuja primeira coluna se refira a valores de B2. (Como sou novo em Macro, essa foi a rotina mais simples que encontrei... talvez seja o caso utilizar filtro avançado, não sei...)

Em geral, eu queria que a cada alteração de valor que ocorrer em qualquer célula do intervalo C2:P2, a rotina preencha automaticamente os valores das células subsquentes àquela que teve seu valor alterado. Lembrando, que deve-se manter a validação de dados. Assim, se o valor "sugerido" (diga-se preenchido) pela macro não corresponder ao valor desejado pelo usuário, ele seja capaz de selecionar um novo valor na lista fornecida pela validação de dados (lembrando que a própria lista de validação irá variar a depender do valor selecionado na célula anterior). Nesse caso, a rotina executaria novamente a macro, atualizando o valor das demais células subsequentes.

Se você conhecer algum outro evento que seja de alteração do valor da célula, ao invés de apenas alteração na seleção da célula, eu agradeço. Como disse, sou totalmente novo na área de VBA...

Mais uma vez, agradeço pelo esforço e paciência.

Abs.,
menezes7
Membro
Membro
 
Mensagens: 11
Registrado em: Seg Jan 08, 2018 4:13 pm
Has thanked: 0 time
Have thanks: 0 time

Anterior

Voltar para VBA & Macros

Quem está online

Usuários navegando neste fórum: Google [Bot] e 2 visitantes