Fóruns
Microsoft Excel - 2...
2024 - VBA & Macros
função soma com var...
 
Notifications
Clear all

função soma com variaveis

 
2024 - VBA & Macros
Post Recente por junior0908 8 anos atrás
13 Posts
4 Usuários
0 Reactions
2,129 Visualizações
RSS
 junior0908
(@junior0908)
Posts: 6
Active Member
Topic starter
 

ola!

gostaria de uma ajuda com meu código. ele funciona como eu quero mas a célula no qual ele esta alocado fica com o nome "#nome?" por nao entender a função soma.
segue dados abaixo:

Sub variable_row()
'
' Variable ROW: Adiciona uma nova linha de controle ao modulo de gastos variaveis.
'
Dim celula As String
Dim resumo As String
Dim plan1 As String
Dim ini1 As String
Dim fim1 As String

Application.ScreenUpdating = False
'

    'inserção de nova coluna na aba detalhe e coleta das posiçoes variaveis
    Sheets("1").Select
    plan1 = ActiveSheet.Name
    Cells.Find(What:="trigger", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    Selection.EntireColumn.Insert
    ActiveCell.Offset(1, 0).Select
    ini1 = ActiveCell.Address(0, 0)
    ActiveCell.Offset(30, 0).Select
    fim1 = ActiveCell.Address(0, 0)
    
    'inserção de nova linha na aba resumo e criação da formula soma
    Sheets("RESUMO").Select
    plan = ActiveSheet.Name
    Cells.Find(What:="GASTOS VARIAVEIS", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Selection.EntireRow.Insert
    ActiveCell.FormulaR1C1 = "New"
    celula = ActiveCell.Address
    ActiveCell.Offset(0, 1).Select
    
    'inicio problema de formula ilegivel
     ActiveCell.FormulaR1C1 = "=soma(" & plan1 & "!" & ini1 & ":" & fim1 & ")"
     ActiveCell.Replace What:="'", Replacement:="", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=False
     Cells.Find(What:="'", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
     xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
     , SearchFormat:=False).Activate
     
     'cofigo segue ok apartir daqui
     Sheets("1").Select
     Cells.Find(What:="trigger", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
     :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
     False, SearchFormat:=False).Activate
     ActiveCell.Offset(0, 1).Select
     ActiveCell.Value = "=" & plan & "!" & celula
     Sheets("resumo").Select

'
Application.ScreenUpdating = False

End Sub

ressalto que se eu clicar na célula problemática, clicar na barra de formulas e pressionar Enter ela funciona como deveria. todavia eu não gostaria de realizar essa etapa para cada nova linha criada.

desde ja agradeço a ajuda,

Sales S junior

 
Postado : 18/12/2016 12:19 am
 srobles
(@srobles)
Posts: 231
Estimable Member
 

Sales, boa tarde!

Em funções / rotinas em VBA, não se utiliza nomenclaturas como fazemos de costume na barra de fórmulas.
No caso, para fazermos uma soma, onde temos em seu código ActiveCell.FormulaR1C1= "=soma(fórmula)", você deve utilizar a função em inglês, ficando ActiveCell.FormulaR1C1 = "=SUM(sua_fórmula)".

Abs

Espero ter ajudado.

Abs.

Saulo Robles

 
Postado : 18/12/2016 12:05 pm
 junior0908
(@junior0908)
Posts: 6
Active Member
Topic starter
 

Srobles a primeira soma funcionou apos a correção. porem uma segunda soma na mesma macro esta dando problema. eu já verifiquei a coleta das variáveis e as posições batem, mas quando ele cria a formula de soma fica algo como soma=($J:$L), sendo que a as posições são C10 e C12. 

    ActiveCell.Offset(0, 1).Select
    soma0 = ActiveCell.Address(0, 0)
    Selection.End(xlDown).Select
    soma1 = ActiveCell.Address(0, 0)
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=sum(" & soma0 & ":" & soma1 & ")"
    ActiveCell.Replace What:="'", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Cells.Find(What:="'", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

a diferença da primeira formula para a segunda é que a célula a receber o valor esta mesclada e já possui um valor em cima que não esta sendo excluído antes da inserção.

 
Postado : 19/12/2016 7:51 am
DJunqueira
 DJunqueira
(@djunqueira)
Posts: 772
Prominent Member
 

Vc não precisa do (0, 0)

soma0 = ActiveCell.Address
soma1 = ActiveCell.Address

Se sua dúvida foi respondida marque o tópico como RESOLVIDO usando o botão com marca verde.

 
Postado : 19/12/2016 8:23 am
 junior0908
(@junior0908)
Posts: 6
Active Member
Topic starter
 

DJunqueira

na verdade esse valor (0,0) não influencia. descobri que o problema esta nas células ao qual a variavel vai coletar os address, estou executando uma função soma em cima de outra função soma, tudo por macro. o problema ocorre porque nessas células(C10:C12) tem formulas de soma em seu interior. eu apenas criei uma referencia(na coluna D, linha 10 com valor (=c10) e linha 12 da mesma coluna como (=C12)) e apliquei uma nova formula de soma na referencia e funcionou.

eu gostaria de saber se é possível reverter esse problema, não gostaria de mais uma coluna na minha planilha, mesmo que ela vá ficar oculta.

 
Postado : 19/12/2016 9:39 am
DJunqueira
 DJunqueira
(@djunqueira)
Posts: 772
Prominent Member
 

Muitas vezes é possível se livrar de uma coluna auxiliar aperfeiçoando a fórmula.

Se sua dúvida foi respondida marque o tópico como RESOLVIDO usando o botão com marca verde.

 
Postado : 19/12/2016 1:39 pm
 srobles
(@srobles)
Posts: 231
Estimable Member
 

Sales, boa noite!

Creio que o seu problema esteja ocorrendo, devido á planilha possuir uma célula mesclada. Repare que seu código possui uma função de selecionar até a última célula preenchida ** Selection.End(xlDown).Select **, e por sua vez, este comando seleciona a célula mesclada, causando o problema com as referências citadas acima por você.

Se possível, poste seu modelo para que possamos analisar melhor o que ocorre.

Abs

Espero ter ajudado.

Abs.

Saulo Robles

 
Postado : 19/12/2016 5:36 pm
 junior0908
(@junior0908)
Posts: 6
Active Member
Topic starter
 

boa noite Srobles!

no teste realizado abaixo sem nenhuma célula mesclada o problema tornou a aparecer!

fiz uma nova planilha, preenchi uma matriz [9,3] com valores aleatórios e então executei 3 formulas de soma , sendo uma para cada coluna. para adequar o exemplo a minha planilha utilizei o "variavel = activecell.address" para coletar as posições do intervalo,sendo elas a posição 1 e 9 de cada coluna. depois realizei as três formulas de soma em células individuais para então realizar a quarta soma cujo resultado é o acumulado das formulas soma. segue código abaixo:

Sub Macro1()
' Macro1 Macro
Dim A, AA, B, BB, C, CC, S, SS As String

Range("a1").Select
A = ActiveCell.Address(0, 0)
Range("a9").Select
AA = ActiveCell.Address(0, 0)
Range("B1").Select
B = ActiveCell.Address(0, 0)
Range("B9").Select
BB = ActiveCell.Address(0, 0)
Range("C1").Select
C = ActiveCell.Address(0, 0)
Range("C9").Select
CC = ActiveCell.Address(0, 0)
Range("E3").Select
ActiveCell.FormulaR1C1 = "=SUM(" & A & ":" & AA & ")"
ActiveCell.Replace What:="'", Replacement:=""
S = ActiveCell.Address(0, 0)
Range("E4").Select
ActiveCell.FormulaR1C1 = "=SUM(" & B & ":" & BB & ")"
ActiveCell.Replace What:="'", Replacement:=""
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(" & C & ":" & CC & ")"
ActiveCell.Replace What:="'", Replacement:=""
SS = ActiveCell.Address(0, 0)
Range("E6").Select
ActiveCell.FormulaR1C1 = "=SUM(" & S & ":" & SS & ")"
ActiveCell.Replace What:="'", Replacement:=""
'
End Sub

Obs: é correto usar o [ActiveCell.Replace What:="'", Replacement:=""] apos a formula soma? minha formula vem com apostrofes entre cada variavel.
obs2: estou declarando as variáveis corretamente, o certo é string para endereço de celula?

 
Postado : 20/12/2016 9:07 pm
DJunqueira
 DJunqueira
(@djunqueira)
Posts: 772
Prominent Member
 

obs2: estou declarando as variáveis corretamente, o certo é string para endereço de celula?

Não, o correto é range.

Se sua dúvida foi respondida marque o tópico como RESOLVIDO usando o botão com marca verde.

 
Postado : 21/12/2016 12:35 am
EdsonBR
 EdsonBR
(@edsonbr)
Posts: 1057
Noble Member
 

Olá, Júnior.

O que percebi é que vc tem usado para alimentar as fórmulas endereços de célula na notação no estilo de referência A1 (retornados por Address ou Address(0,0)) e no entanto, ao inserir a fórmula via código, vc usa a propriedade .FormulaR1C1, que usa a notação no estilo de referência R1C1. Deveria, portanto, estar usando a propriedade .Formula ao invés disso. Se tivesse feito isso, seriam desnecessárias as etapas de remover as aspas simples após inseridas as fórmulas, o que já responde a uma de suas perguntas finais:

...Obs: é correto usar o [ActiveCell.Replace What:="'", Replacement:=""] apos a formula soma? minha formula vem com apostrofes entre cada variavel.

Melhor ainda seria usar .FormulaLocal, pois aí nem precisaria traduzir a fórmula para inglês, usaria ela em português mesmo. Assim, em seu primeiro código, poderia usar:
ActiveCell.FormulaLocal = "=soma(" & plan1 & "!" & ini1 & ":" & fim1 & ")"

...uma segunda soma na mesma macro esta dando problema. eu já verifiquei a coleta das variáveis e as posições batem, mas quando ele cria a formula de soma fica algo como soma=($J:$L), sendo que a as posições são C10 e C12.

Isso é efeito do uso da notação R1C1. O código interpreta C10 como Coluna 10 (Coluna J portanto) e C12 como Coluna 12 (Coluna L).

...no teste realizado abaixo sem nenhuma célula mesclada o problema tornou a aparecer!
...
Sub Macro1()
...
Range("C1").Select
C = ActiveCell.Address(0, 0)
Range("C9").Select
CC = ActiveCell.Address(0, 0)
...
ActiveCell.FormulaR1C1 = "=SUM(" & C & ":" & CC & ")"
ActiveCell.Replace What:="'", Replacement:=""
End Sub

Mesmo problema. Quando encontra C1 entende como Coluna 1 (A) e C9 como coluna 9 (I).

Insistindo nesta notação R1C1, haverá problema sempre que o endereço retornar strings com o conteúdo RxCy (R de "Row" e C de "Column") com x e y inteiros. Outro cuidado seria se fosse usar a propriedade .FormulaR1C1Local com o conteúdo na forma LxCy (L de Linha e C de Coluna).

...obs2: estou declarando as variáveis corretamente, o certo é string para endereço de celula?

De fato, a propriedade Address retorna um String. Entretanto, vc poderia economizar muitas linhas de código e tempo de processamento ao trabalhar com métodos do objeto Range, por exemplo, como citado pelo Junqueira, evitando a trabalheira de Selecionar célula - guardar endereço - executar algo no endereço - ativar outra célula. Pode-se fazer numa etapa só.

______
Para detalhes das diferenças entre os os estilos A1 x R1C1 veja "Usando referências em fórmulas do Excel" em https://support.office.com/pt-BR/article/Vis%C3%A3o-geral-de-f%C3%B3rmulas-no-Excel-ecfdc708-9162-49e8-b993-c311f47ca173

 
Postado : 21/12/2016 7:08 am
 junior0908
(@junior0908)
Posts: 6
Active Member
Topic starter
 

EdsonBR

agora ficou claro, obrigado pela aula! tentei usar essa tecnica com a formula SE mas nao funcionou. saberia dizer o que errei?

ActiveCell.Formula = _
"= SE(I1=""janeiro"";SOMA('1'!" & X & ":" & Y & ");SE(I1=""fevereiro"";SOMA('2'!" & X & ":" & Y & ");SE(I1=""março"";SOMA('3'!" & X & ":" & Y & _
");SE(I1=""abril"";SOMA('4'!" & X & ":" & Y & ");SE(I1=""maio"";SOMA('5'!" & X & ":" & Y & ");SE(I1=""junho"";SOMA('6'!" & X & ":" & Y & _
");SE(I1=""julho"";SOMA('7'!" & X & ":" & Y & ");SE(I1=""agosto"";SOMA('8'!" & X & ":" & Y & ");SE(I1=""setembro"";SOMA('9'!" & X & ":" & Y & _
");SE(I1=""outubro"";SOMA('10'!" & X & ":" & Y & ");SE(I1=""novembro"";SOMA('11'!" & X & ":" & Y & "));SOMA('12'!" & X & ":" & Y & "))))))))))))"
 
Postado : 23/12/2016 5:55 pm
DJunqueira
 DJunqueira
(@djunqueira)
Posts: 772
Prominent Member
 

Fórmula p/ retornar o número de um mês escrito por extenso.

=MÊS(DATA.VALOR("1-"&ESQUERDA("janeiro";3)&"-2016"))

Se sua dúvida foi respondida marque o tópico como RESOLVIDO usando o botão com marca verde.

 
Postado : 24/12/2016 5:25 pm
 junior0908
(@junior0908)
Posts: 6
Active Member
Topic starter
 

boa tarde pessoal! agradeço a ajuda de todos, solucionei o problema do IF com o codigo

    ActiveCell.Formula = _
        "=IF(" & Z & "=""caso 1"",SUM('1'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 2"",SUM('2'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 3"",SUM('3'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 4"",SUM('4'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 5"",SUM('5'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 6"",SUM('6'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 7"",SUM('7'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 8"",SUM('8'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 9"",SUM('9'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 10"",SUM('10'!" & X & ":" & Y & "),IF(" & _
        Z & "=""caso 11"",SUM('11'!" & X & ":" & Y _
        & "),SUM('12'!" & X & ":" & Y & "))))))))))))"
 
Postado : 25/12/2016 3:46 pm
Forum Jump:
  Tópico Anterior
Próximo Tópico