programação VBA - somar valores de colunas de forma dinâmica

[robertocaeiro]

Prezados,

segue planilha em anexo como exemplo, fiz um sistema onde controlo "N" estoques de forma individual e consolido resultados na planilha controle com o intuito de fazer uma curva ABC depois. Ocorre que os estoques são gerados de forma dinâmica, de acordo com o usuário do sistema, e na planilha controle gero entradas e saídas (uma ao lado da outra. Estes valores vem dos estoques individuais, e precedem a Letra E- para entradas e a letra S- para as saídas. Coloquei uma formula no módulo que faz a soma (R1C1) e joga na coluna da soma das entradas, somando todas as entradas, e uma formula nas saídas, pegando a soma das saídas. Ocorre que quando o usuário cria um estoque e depois quer removê-lo, dou baixa de todos os itens e depois tenho de remover o mesmo desta planilha, dando baixa (excluindo o par de colunas, entrada e saída daquele estoque), e quando isso ocorre, eu perco a referencia da formula (R1C1) de soma de entradas e saídas. Como posso resolver este problema, deixando o intervalo dinâmico para receber as somas necessárias e a exclusões sem perder a referencia das formulas de soma ? espero ter deixado claro o problema, agradeço quem puder me dar uma luz. Pensei em fazer uma função que soma-se com loop buscando (extrair substring E de entradas) e soma-se num contador e devolve-se a função na célula , mostrando sempre a soma atualizada, assim não me preocuparia com referencias das funções do excel. Mais não fiz nenhuma função ainda.  Estou acostumado a trabalhar com BD e no excel tudo fica um pouco diferente, pois tenho de controlar tudo no braço. Obrigado.

resumo controle estoques.xlsx

[CasaDoHardware]

Nem via a planilha mas pela descrição do problema uma tabela dinamica talvez resolva

[robertocaeiro]

É que eu uso essa planilha para fazer uma listview dinâmica com a posição de todos os estoques. Será que usando função eu conseguiria o mesmo efeito da formula ? Se eu fizer uma função  buscando dinamicamente os estoques e dando a soma.

Todos os processos de criação de novos estoques são controlados pelo usuário, o programa vai no controle geral e gera o novo estoque sozinho, e da mesma forma o mesmo é excluído, por isso seria interessante ter esta soma de forma fixa sempre varrendo as colunas e somando os estoques individuais para a totalização do geral.

 

[CasaDoHardware]

você nao mandou nenhum codigo vba na planilha. Isso faz diferença na hora da solução.

 

Poste a o exemplo com mais informações inclusive com o formulario em questao e o codigo vba que faz ele funcionar.

 

 

Isso permite que se ajuste o codigo para que eventualmente as somas sejam feitas por vba.

[robertocaeiro]

Sub salvar_dados_produto()

    Application.ScreenUpdating = False
    
    Sheets("produto").Select

    Do
        If ActiveCell.Value <> Empty Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until ActiveCell.Value = Empty
    
'===================================================================
    'GRAVA OS DADOS DO FORM MA PLANILHA PRODUTOS
    
    ActiveCell.Offset(0, 0).Value = TextBox_id.Text
    ActiveCell.Offset(0, 1).Value = TextBox_cod_produto.Text
    ActiveCell.Offset(0, 2).Value = CDate(TextBox_data.Value)
    ActiveCell.Offset(0, 3).Value = UCase(TextBox_nome_produto.Text)
    ActiveCell.Offset(0, 4).Value = UCase(TextBox_fabricante.Text)
    ActiveCell.Offset(0, 5).Value = UCase(TextBox_descricao_produto.Text)
    ActiveCell.Offset(0, 6).Value = UCase(ComboBox_tipo_material.Text)
    ActiveCell.Offset(0, 7).Value = UCase(ComboBox_fornecedor_nome.Text)
    ActiveCell.Offset(0, 8).Value = UCase(usuario)
    ActiveCell.Offset(0, 9).Value = UCase(TextBox_obs.Text)
    ActiveCell.Offset(0, 10).Value = TextBox_minimo.Value
    '===================================================================
    'APÓS SALVAR O NOVO PRODUTO, O PRODUTO É LEVADO PARA A PLANILHA CONTROLE (MOSTRA SALDO GERAL COM TODOS OS ESTOQUES) ,
    'ONDE AGUARDA UMA ENTRADA OU UMA SAÍDA DOS DEMAIS ESTOQUES
   
    Dim lin, codigox, ultimo As Integer
    Dim Myrange As Range
    Dim plan As Worksheet
   
    Sheets("controle").Select
    Range("A2").Select
    
    lin = 2
    Do While Range("A" & lin).Value <> ""
        lin = lin + 1

        If Range("A" & lin).Value = "" Then
            Range("A" & lin).Activate
            ActiveCell.Offset(-1, 0).Select
            ultimo = ActiveCell.Value
            Range("A" & lin).Select
        End If
    Loop

        codigox = ultimo + 1

    ActiveCell.Offset(0, 0).Value = codigox 'id do controle
    ActiveCell.Offset(0, 1).Value = TextBox_cod_produto.Text 'código do produto
    ActiveCell.Offset(0, 2).Value = UCase(TextBox_nome_produto.Text) 'nome do produto
    ActiveCell.Offset(0, 3).Value = TextBox_minimo.Value  'saldo mínimo do produto

''-----------------------------------------------------
'TRECHO NOVO

AQUI VOU COLOCAR ALGUMA FUNÇÃO OU FORMULA PARA SUBSTITUIR A R1C1 ABAIXO, POIS QUANDO UM ESTOQUE É EXCLUÍDO DO SISTEMA, 'AS COLUNAS DE ENTRADA E SAÍDA DA PLANILHA CONTROLE SÃO DELETADAS, GERANDO A PERDA DE REFERENCIA NA FORMULA R1C1 ABAIXO.
UMA SAÍDA SERIA REFAZER A FÓRMULA R1C1 E JOGAR NOVAMENTE APÓS A DELEÇÃO DAS COLUNAS, OU SE CRIAR UMA FUNÇÃO QUE SUBSTITUA'A FÓRMULA R1C1

'FIM TRECHO NOVO

''----------------------------------------------------------------------------------------------------///
    'GRAVA AS FORMULAS DE CÁLCULOS NA PLANILHA
 SOMA  DAS ENTRADAS  

Range("E" & lin).Select
    ActiveCell.FormulaR1C1 = "=sum(RC[5],RC[7],RC[9],RC[11],RC[13],RC[15],RC[17],RC[19],RC[21],RC[23],RC[25],RC[27],RC[29],RC[31],RC[33],RC[35],RC[37],RC[39],RC[41],RC[43],RC[45],RC[47],RC[49],RC[51],RC[53],RC[55],RC[57],RC[59],RC[61],RC[63],RC[65])" 'soma das entradas

 

SOMA DAS SAÍDAS   

Range("F" & lin).Select
    ActiveCell.FormulaR1C1 = "=sum(RC[5],RC[7],RC[9],RC[11],RC[13],RC[15],RC[17],RC[19],RC[21],RC[23],RC[25],RC[27],RC[29],RC[31],RC[33],RC[35],RC[37],RC[39],RC[41],RC[43],RC[45],RC[47],RC[49],RC[51],RC[53],RC[55],RC[57],RC[59],RC[61],RC[63],RC[65])" 'soma das saídas

   

Range("G" & lin).Select
    ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-1])" 'fórmula para cálculo do estoque

   

Range("I" & lin).Select
    ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" 'calcula o valor total no estoque para o produto

    Columns.AutoFit
    'FIM DO CADASTRO DE PRODUTO NA PLANILHA CONTROLE
'----------------------------------------------------------------------------------------------------///

    ' Limpa os campos
    Call limpar_campos
      
    cmd_novo_prod.Enabled = False
    cmd_salvar_new.Visible = False

    Texto = "Produto inserido com sucesso, Deseja cadastrar outro Produto?"
    Título = "Cadastro de Produto"
    CxDialog = MsgBox(Texto, vbYesNo + vbQuestion, Título)
    
    If CxDialog = vbYes Then
        cmd_novo_prod.Enabled = True
        Call atualizar_list_produto
        cmd_novo_prod.Visible = True
        cmd_salvar_new.Visible = False
        ListView2.Enabled = True
        
    ElseIf CxDialog = vbNo Then
        Unload Frm_produto
        Load Frm_menu
        Frm_menu.Show
        
    End If
        
       Application.ScreenUpdating = True

End Sub

 

 

OS OUTROS PROCESSOS APENAS ALIMENTAM A PLANILHA CONTROLE JOGANDO AS ENTRADAS E SAÍDAS

DE ACORDO COM O ESTOQUE ESPECÍFICO.

[CasaDoHardware]

 @robertocaeiro Tenho a impressão que você nao leu todo o o post que fiz. Vou repetir

 

Poste o ARQUIVO COM O FORMULARIO E O CODIGO FUNCIONANDO, assim é mais fácil ajudar e ajustar o codigo que você ja esta usando, ou ate reescrever o codigo.

[robertocaeiro]

Casa Do Hardware, obrigado pela atenção, resolvi escrevendo um código que monta a fórmula dinamicamente de acordo com as colunas existentes, já testei e está funcionando perfeitamente. 

segue o código:

Private Sub grava_formula_soma() 'montar formula com base nos estoques existentes

    Dim mysoma_ent, mysoma_ent_1 As String
    Dim mysoma_sai, mysoma_sai_1 As String
    Dim cel_letra, cel_letra1 As String
    Dim i As Integer
    Dim var1, var2 As Variant
    Dim Ultima_coluna As Variant
    
    Sheets("Controle").Select
    Range("J1").Select
    
    Ultima_coluna = Sheets("Controle").UsedRange.Columns.Count
    Ultima_coluna = Ultima_coluna - 9
    mysoma_ent = "=soma("
    mysoma_sai = "=soma("
   
    For i = 1 To Ultima_coluna
       
        var1 = ActiveCell.Value
        var2 = ActiveCell.Value
       
            opera = Left(var1, 1)
                If opera = "E" Then
                    cel_letra = ActiveCell.Address
                    mysoma_ent_1 = cel_letra & ";"
                    mysoma_ent_1 = Replace(mysoma_ent_1, "$", "")
                    mysoma_ent = mysoma_ent + mysoma_ent_1
                End If
                
            opera1 = Left(var2, 1)
                If opera1 = "S" Then
                    cel_letra1 = ActiveCell.Address
                    mysoma_sai_1 = cel_letra1 & ";"
                    mysoma_sai_1 = Replace(mysoma_sai_1, "$", "")
                    mysoma_sai = mysoma_sai + mysoma_sai_1
                End If
       
        ActiveCell.Offset(0, 1).Select 'vai a próxima coluna
    Next
    '--------------------------------------------------------------------------------------------------------
    ' AJUSTA A FORMULA DAS ENTRADAS
    
    mysoma_ent = mysoma_ent & ")"
    mysoma_ent = Replace(mysoma_ent, ";)", ")")
    Sheets("controle").Select
    mysoma_ent = Replace(mysoma_ent, "1", 2)
    ActiveCell.Activate
    Range("E" & 2).Select
    ActiveCell.FormulaLocal = mysoma_ent
    Selection.AutoFill Destination:=Range("E2:E" & Range("A1000000").End(xlUp).Row), Type:=xlFillDefault
    
    '--------------------------------------------------------------------------------------------------------
    ' AJUSTA A FORMULA DAS SAÍDAS
    
    mysoma_sai = mysoma_sai & ")"
    mysoma_sai = Replace(mysoma_sai, ";)", ")")
    Sheets("controle").Select
    mysoma_sai = Replace(mysoma_sai, "1", 2)
    ActiveCell.Activate
    Range("F" & 2).Select
    ActiveCell.FormulaLocal = mysoma_sai
    Selection.AutoFill Destination:=Range("F2:F" & Range("A1000000").End(xlUp).Row), Type:=xlFillDefault
    
End Sub