Formulas VBA Somases nas linhas com valores

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

Formulas VBA Somases nas linhas com valores

Mensagempor Alex87 » Qui Jan 16, 2020 7:25 am

Bom dia Galera, estou precisando de uma ajuda.

Estou aprendendo usar vba, e estou me divertindo muito com as possibilidades existentes, mas estou ainda muito limitado.

vamos lá ao que preciso.

Estou criando uma planilha que faz uma analise de duas bases diferentes. Com isso preciso de fazer alguns processos até chegar ao final que preciso.
e estou agarrando, pq nesses processos possuem formulas.
Já consegui concluir um modelo, porem está lento de mais para carregar, pois como são muitos dados ela trava. então estava pensando em como otimizar.

Eu tenho formulas do tipo somases e indice + corresp. a minha forma amadora de trabalhar, eu criei a formula e deixei uma celula, e faço o comando para caso a celula x tenha valor ele copia dessa celula a formula e cola nas linhas selecionadas. e ai que agarra o processo.

Acredito que se conseguir montar a formula para o proprio comando executar e só lançar o valor no campo o excel não vai tentar calcular e isso otimizará a execução.

Vamos as formulas que tenho e que preciso criar no proprio vba

=SOMASES(MovSped!G:G;MovSped!A:A;'Dif NF'!O7;MovSped!B:B;'Dif NF'!P7;MovSped!E:E;'Dif NF'!R7)

essa formula deve ser aplicada sempre na coluna "T" se a célula "O" tiver valor. ex. se a O2 tiver valor então a T2 recebe o calculo, se a O3 tiver valor a T3 recebe o calculo e assim sucessivamente. não existe pulo de linha, a linha é sequencial, mas pode se ter até 65mil linhas.

=SE(CONT.SES(W:W;O9;X:X;P9;Z:Z;R9)=0;"NF N Lançada";SE(CONT.SES(W:W;O9;X:X;P9;Z:Z;R9)=1;"";SE(CONT.SES(W:W;O9;X:X;P9;Z:Z;R9)>=2;"Duplicidade")))

essa formula deve ser aplicada sempre na coluna "N" se a célula "O" tiver valor. ex. se a O2 tiver valor então a N2 recebe o calculo, se a O3 tiver valor a N3 recebe o calculo e assim sucessivamente. não existe pulo de linha, a linha é sequencial, mas pode se ter até 65mil linhas.

=SEERRO(PROCV(J9;A:B;2;FALSO);"0")

essa formula deve ser aplicada sempre na coluna "K" se a célula "J" tiver valor. ex. se a J2 tiver valor então a K2 recebe o calculo, se a J3 tiver valor a K3 recebe o calculo e assim sucessivamente. não existe pulo de linha, a linha é sequencial, mas pode se ter até 65mil linhas.

=SEERRO(ÍNDICE(Y:Y;CORRESP(B7;V:V;0));"0")

essa formula deve ser aplicada sempre na coluna "K" se a célula "J" tiver valor. ex. se a J2 tiver valor então a K2 recebe o calculo, se a J3 tiver valor a K3 recebe o calculo e assim sucessivamente. não existe pulo de linha, a linha é sequencial, mas pode se ter até 65mil linhas.

ve se deu pra entender.

abraços galera.
Alex87
Membro
Membro
 
Mensagens: 2
Registrado em: Qui Jan 16, 2020 7:07 am
Has thanked: 0 time
Have thanks: 0 time

{ SO_SELECT }

Re: Formulas VBA otimizando planilha

Mensagempor wagner » Qui Jan 16, 2020 7:57 am

Alex87,

Bom dia!

Vou iniciar o pontapé aqui na bola e você cabeceia e faz o gol OK?

Para simplificar a coisa vamos usar a função SOMA. Depois é só você usar a mesma lógica para funções mais complexas. Não sei te dizer se inserir as funções diretamente pelo VBA vai tornar o processo mais rápido pois o processador vai ter que executar os mesmos cálculos também pelo código VBA. Mas... teste!

Imagine uma planilha que na na coluna A você tenha os seguintes valores em A1, A2 e A3: 1, 2 e 3. Imagine Também que você quer inserir em A4 o resultado da soma da faixa de A1 a A3. Esse resultado seria igual a 6 e uma das formas de fazer com a função SOMA seria inserir na célula A4 a função dessa forma: =SOMA(A1:A3). Vou mostrar, então, como usar o VBA para inserir essa mesma função na célula A4. Farei de um modo que seja independente do número de linhas que a coluna venha a ter, sempre, ao final, da coluna, será inserida a fórmula para que o VBA/Excel calcule a soma dos valores inseridos em toda a coluna. Teríamos então o seguinte código:

Código: Selecionar todos
Sub SomarColuna()
    '=============================================================================
    'Código desenvolvido por Wagner Morel em 16/01/2020 para Alex87 do Planilhando
    '=============================================================================
   
    'Declarando as variáveis
    Dim i As Long
    Dim UltimaLinha As Long
   
    'Descobrindo qual é a última linha com dados pela coluna A
    UltimaLinha = Sheets("Plan1").Cells(Cells.Rows.Count, 1).End(xlUp).Row
       
    'Inserindo a fórmula de SOMA na última linha
    Sheets("Plan1").Range("A" & UltimaLinha + 1).FormulaLocal = "=SOMA(A1:A" & UltimaLinha & ")"
End Sub
Se a resposta foi útil para você, por gentileza, Amigo, clique na mãozinha ao lado direito da ferramenta CITAR, no canto superior direito.

Atenciosamente
Wagner Morel
Avatar do usuário
wagner
Moderador
Moderador
 
Mensagens: 5547
Registrado em: Sáb Out 20, 2012 12:49 pm
Localização: Fortaleza - CE
Has thanked: 104 times
Have thanks: 2373 times

Re: Formulas VBA otimizando planilha

Mensagempor Alex87 » Qui Jan 16, 2020 5:49 pm

Oi Wagner, obrigado pela resposta, mas não seria exatamente isso.

eu tenho 3 colunas A B C, eu preciso que se a A1 for diferente de vazio, a C1 tenha a formula. independente de quantas linhas tiver, sempre que na coluna "A" tiver algum valor a coluna "C" faça a formula.
Alex87
Membro
Membro
 
Mensagens: 2
Registrado em: Qui Jan 16, 2020 7:07 am
Has thanked: 0 time
Have thanks: 0 time

Re: Formulas VBA Somases nas linhas com valores

Mensagempor ewshaka » Qui Jan 16, 2020 8:58 pm

Boa noite.
Pelo que entendi, o recurso abaixo deve te ajudar.

Código: Selecionar todos
Sub Calcular()
Dim linha As Single
linha = 2

Do Until Sheets("Planilha1").Cells(linha, 1) = "" 'Loop para percorrer a planilha até achar a linha vazia - Coloquei a coluna A, mas você pode adaptar a coluna que sempre terá um dado
    If Not IsNull(Sheets("Planilha1").Cells(linha, 1)) Or Not (Sheets("Planilha1").Cells(linha, 1) = "") Then 'Verifica se a celula da coluna a está preenchida
        Sheets("Planilha1").Cells(linha, 3) = WorksheetFunction.Sum(Sheets("Planilha1").Range("A2:A" & linha))
    End If
linha = linha + 1
Loop
End Sub


A partir desse exemplo, vá adaptando ao que precisa.
Qualquer duvida, da um grito. E poste um modelo da sua planilha, para elucidarmos melhor.
ewshaka
Membro
Membro
 
Mensagens: 34
Registrado em: Seg Nov 11, 2019 11:30 am
Has thanked: 2 times
Have thanks: 14 times

Re: Formulas VBA Somases nas linhas com valores

Mensagempor wagner » Sex Jan 17, 2020 3:26 pm

Alex87,

Boa tarde!

De fato... você não entendeu o que eu quis te passar. Eu te ensinei a pescar. Não dei o peixe. O raciocínio é o mesmo. Seria algo como isso:
Código: Selecionar todos
Sub Teste ()
     Dim i as Long

     i = 2
     If Range("A" & i).Value <> "" Then
          Range("C" & i).FormulaLocal = "=SE... 'aqui você vai digitar sua fórmula, substituindo os números das linhas fixas pela variável i
          i = i +1
     End If
End Sub
Se a resposta foi útil para você, por gentileza, Amigo, clique na mãozinha ao lado direito da ferramenta CITAR, no canto superior direito.

Atenciosamente
Wagner Morel
Avatar do usuário
wagner
Moderador
Moderador
 
Mensagens: 5547
Registrado em: Sáb Out 20, 2012 12:49 pm
Localização: Fortaleza - CE
Has thanked: 104 times
Have thanks: 2373 times


Voltar para VBA & Macros

Quem está online

Usuários navegando neste fórum: Bing [Bot] e 5 visitantes