Soma activecell.adress


#1

Boa Tarde,

Sou iniciante em VBA e estou com um problema, pois preciso que na célula B12 seja somada os endereços de outras células como uma soma normal seria, mas tem um porem, pois os valores a serem somados são de células que não sei o endereço ainda, pois são endereços que são gerados através de um For To.

Sub Nova_campanha()

’ Preenchimento Macro
’ Preenchimento de planilhas de campanhas

’ Atalho do teclado: Ctrl+w

Sheets.Add After:=Worksheets(Worksheets.Count) 'Inclui planilha como ultima pasta de trabalho

UserForm1.Show

If Range("d1").Value = 0 Then

ActiveWindow.SelectedSheets.Delete
Exit Sub
End If

'PREENCHER CABEÇALHO

Range(“a1”).Select
ActiveCell.FormulaR1C1 = “EMPRESA”
Range(“a2”).Select
ActiveCell.FormulaR1C1 = “COMERCIAL”
Range(“a4”).Select
ActiveCell.FormulaR1C1 = “PREMIAÇÃO”
Range(“a5”).Select
ActiveCell.FormulaR1C1 = “TX. ADM”
Range(“a7”).Select
ActiveCell.FormulaR1C1 = “SALDO”
Range(“a8”).Select
ActiveCell.FormulaR1C1 = “TX DEVOLUÇÃO SALDO”
Range(“c1”).Select
ActiveCell.FormulaR1C1 = “CAMPANHA”
Range(“c2”).Select
ActiveCell.FormulaR1C1 = “CONTATO”
Range(“c4”).Select
ActiveCell.FormulaR1C1 = “%PREMIAÇÃO”
Range(“c7”).Select
ActiveCell.FormulaR1C1 = “%SALDO”
Range(“e1”).Select
ActiveCell.FormulaR1C1 = “TIPO”
Range(“e2”).Select
ActiveCell.FormulaR1C1 = “STATUS”
Range(“e4”).Select
ActiveCell.FormulaR1C1 = “SETUP”
Range(“e5”).Select
ActiveCell.FormulaR1C1 = “FEE”
Range(“g1”).Select
ActiveCell.FormulaR1C1 = “INÍCIO”
Range(“g2”).Select
ActiveCell.FormulaR1C1 = “TERMINO”
Range(“g4”).Select
ActiveCell.FormulaR1C1 = “BV IS2B”
Range(“g5”).Select
ActiveCell.FormulaR1C1 = “BV PARCEIROS”
Range(“i1”).Select
ActiveCell.FormulaR1C1 = “DURAÇÃO”
Range(“i4”).Select
ActiveCell.FormulaR1C1 = “%PREMIAÇÃO”
Range(“i5”).Select
ActiveCell.FormulaR1C1 = “%PREMIAÇÃO”
Range(“i8”).Select
ActiveCell.FormulaR1C1 = “ESTIMATIMADO”
Range(“k4”).Select
ActiveCell.FormulaR1C1 = “OUTROS SERVIÇOS”

'PREENCHER QUADRO

Range(“f11”).Select
ActiveCell.FormulaR1C1 = “MÊS”
Range(“f12”).Select
ActiveCell.FormulaR1C1 = “SITUAÇÃO”
Range(“f13”).Select
ActiveCell.FormulaR1C1 = “PREMIAÇÃO”
Range(“f14”).Select
ActiveCell.FormulaR1C1 = “TX. ADM”
Range(“f15”).Select
ActiveCell.FormulaR1C1 = “SETUP”
Range(“f16”).Select
ActiveCell.FormulaR1C1 = “FEE”
Range(“f17”).Select
ActiveCell.FormulaR1C1 = “BV IS2B”
Range(“f18”).Select
ActiveCell.FormulaR1C1 = “BV PARCEIROS”
Range(“f19”).Select
ActiveCell.FormulaR1C1 = “OUTROS SERVIÇOS”

'PREENCHER CABEÇALHO ARRECADADO NO MÊS

Range(“f12:f19”).Select
Selection.Copy
Range(“a11”).Select
ActiveSheet.Paste
Range(“b11”).Select
ActiveCell.FormulaR1C1 = “PREVISTO”
Range(“C11”).Select
ActiveCell.FormulaR1C1 = “REALIZADO”
Range(“D11”).Select
ActiveCell.FormulaR1C1 = “DIFERENÇA”

'PREENCHER CABEÇALHO QUADRO

Range(“g12”).Select
ActiveCell.FormulaR1C1 = “PREVISTO (CONTRATO)”
Range(“h12”).Select
ActiveCell.FormulaR1C1 = “SALDO MÊS ANTERIOR”
Range(“i12”).Select
ActiveCell.FormulaR1C1 = “REALIZADO”
Range(“j12”).Select
ActiveCell.FormulaR1C1 = “DIFERENÇA”

Range(“g11”).Select
ActiveWindow.FreezePanes = True

'CALCULOS CABEÇALHO

'estimado
Range(“j8”).Select
ActiveCell.FormulaR1C1 = “=r5c2+r4c6+r5c6+r4c8+r5c8+r8c2”
'duração
Range(“a1”).Select

Range("j1").Select
ActiveCell.FormulaR1C1 = "=((R2C8 - R1C8) / 30) + 1"
Selection.NumberFormat = "0"

'$taxa
Range(“b5”).Select
ActiveCell.FormulaR1C1 = “=r4c2r4c4"
'taxa devolução de saldo
Range(“b8”).Select
ActiveCell.FormulaR1C1 = "=r7c2
r7c4”
'BV IS2B
Range(“h4”).Select
ActiveCell.FormulaR1C1 = “=(r4c2*r4c10)3%"
'BV parceiros
Range(“h5”).Select
ActiveCell.FormulaR1C1 = "=(r4c2
r5c10)*3%”

'Calculos corpo

'Previsto (contrato)
Range(“g13”).Select
ActiveCell.FormulaR1C1 = “=r4c2/r1c10”
Range(“g14”).Select
ActiveCell.FormulaR1C1 = “=r5c2/r1c10”
Range(“g15”).Select
ActiveCell.FormulaR1C1 = “=r4c6/r1c10”
Range(“g16”).Select
ActiveCell.FormulaR1C1 = “=r5c6/r1c10”
Range(“g17”).Select
ActiveCell.FormulaR1C1 = “=r4c8/r1c10”
Range(“g18”).Select
ActiveCell.FormulaR1C1 = “=r5c8/r1c10”
Range(“g19”).Select
ActiveCell.FormulaR1C1 = “=r5c8/r1c10”
'Diferença
Range(“j13”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-3]-r[0]c[-1]”
Range(“j14”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-3]-r[0]c[-1]”
Range(“j15”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-3]-r[0]c[-1]”
Range(“j16”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-3]-r[0]c[-1]”
Range(“j17”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-3]-r[0]c[-1]”
Range(“j18”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-3]-r[0]c[-1]”
Range(“j19”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-3]-r[0]c[-1]”

'Diferença 2º bloco
Range(“g12:j19”).Select
Selection.Copy
Range(“g12”).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveSheet.Paste
Range(“j13”).Select
ActiveCell.FormulaR1C1 = “=(r[0]c[-3]+r[0]c[-2])-r[0]c[-1]”
Range(“j14”).Select
ActiveCell.FormulaR1C1 = “=(r[0]c[-3]+r[0]c[-2])-r[0]c[-1]”
Range(“j15”).Select
ActiveCell.FormulaR1C1 = “=(r[0]c[-3]+r[0]c[-2])-r[0]c[-1]”
Range(“j16”).Select
ActiveCell.FormulaR1C1 = “=(r[0]c[-3]+r[0]c[-2])-r[0]c[-1]”
Range(“j17”).Select
ActiveCell.FormulaR1C1 = “=(r[0]c[-3]+r[0]c[-2])-r[0]c[-1]”
Range(“j18”).Select
ActiveCell.FormulaR1C1 = “=(r[0]c[-3]+r[0]c[-2])-r[0]c[-1]”
Range(“j19”).Select
ActiveCell.FormulaR1C1 = “=(r[0]c[-3]+r[0]c[-2])-r[0]c[-1]”
ActiveSheet.Select

'saldo mês anterios 2º bloco
Range(“l13”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-2]”
Range(“l14”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-2]”
Range(“l15”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-2]”
Range(“l16”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-2]”
Range(“l17”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-2]”
Range(“l18”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-2]”
Range(“l19”).Select
ActiveCell.FormulaR1C1 = “=r[0]c[-2]”

'Formatação valores
Range(“h5”).Select
Selection.Style = “Comma”
Range(“j8”).Select
Selection.Style = “Comma”
Range(“h4”).Select
Selection.Style = “Comma”
Range(“g13:j19”).Select
Selection.Style = “Comma”
Range(“k12:n19”).Select
Selection.Style = “Comma”

'Repetição

For B = 1 To Range("j1").Value - 1
Range("G11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R1C8"
Range("G11").Select
Selection.NumberFormat = "mmmm"
Selection.Copy
Range("H11:J11").Select
ActiveSheet.Paste
Range("G11").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, -3).Range("A1:D1").Select
ActiveCell.Activate
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[]C[-1]+31"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[]C[-2]+31"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[]C[-3]+31"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[]C[-4]+31"
ActiveCell.Offset(0, 1).Range("A1").Select
Range("G11").Select

Next B

For C = 1 To Range("j1").Value - 2

'3ºbloco
Range(“k12:n19”).Select
Selection.Copy
Range(“g12”).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveSheet.Paste

**Range("G12").Select**

** Selection.End(xlToRight).Select**
** ActiveCell.Offset(1, -3).Range(“A1”).Select**
** Range(“b12”).Value = ActiveCell.Address** ’ Aqui preciso que pra cada valor das células que vão ser geradas, esse valor venha se adicionado a g12, sem perder o vinculo, pois trata se de um quadro de acompanhamento.

Next C


ActiveSheet.Name = Range("b2").Value & "-" & Range("d1").Value 'altera o nome da aba para o mesmo da campanha


Cells.Select
Cells.EntireColumn.AutoFit

Range("a1").Select



Application.ScreenUpdating = False
Dim WkSheet As Worksheet
Dim NomePlan As String
Dim I As Integer
Ultima = Worksheets(Worksheets.Count).Name
For Each WkSheet In Worksheets
NomePlan = LCase(WkSheet.Name)
For I = 1 To Worksheets.Count
If LCase(Worksheets.Item(I).Name) < NomePlan Then
WkSheet.Move After:=Worksheets.Item(I)
End If
Next I
Next WkSheet
Application.ScreenUpdating = True

Line1:

End Sub
Sub finalizar()
Exit Sub
End Sub

Esse é todo meu código, mas a parte que preciso esta em negrito.

Grato desde já.


#2

@GTmacieira, bem difícil te entregar exatamente o que você precisa sem depurar, visualizar e entender os objetivos e resultados esperados. Mas suspeito que o que deseja seja algo assim:

Range(“b12”).Value = Range(“b12”).Formula & "+" & ActiveCell.Address


#3

Bom dia @xavier, atende quase que plenamente minha necessidade, vou tentar adequar ao meu código, e deixo um parecer aqui no fórum. Por hora muito obrigado