Set range and calculate sum problem

[Nugetti]

I'm trying to calculate a sum out of a set range selection. The problem is pretty simple so I've really started riping my hair out thinking why this does not work

Dim MyRange As Range
'Moving to certain position at the table and selecting and setting the range
Range("E2").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).Select
Set MyRange = Selection
' Moving to the position where the sum formula is then written
Selection.Offset(0, 1).Select
Selection.End(xlDown).Select
Selection.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(MyRange)"

The problem is that the sum formula here only writes =SUM(Myrange) in to the field. I also tried setting the start and end cells as Strings and making the formula as ..= "=SUM(STARTCELL:ENDCELL)" ending up with the same conclusion. There is probably a very simple solution for this.

So the summed range is D5 : D8 and the conclusion should be in F8.

Capture.JPG

This small macro part of a larger macro chain with various tables so the range changes over and over again when I put this part to a loop later on. That's why ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-2]:RC[-2])" is not a solution

Thanks for your help!

[patel]

Your goal is not clear for me, attach please a sample file with data and desired result

[HaHoBe]

Hi, Nugetti,

please wrap your procedure in code tags for better readability.

From what I see from the picture and understand from the code:

Sub VBA_EX_47489()
Dim MyRange As Range
'Moving to certain position at the table and selecting and setting the range
With Range("E2")
  Set MyRange = Range(.Offset(0, -1), .End(xlDown).Offset(0, -1).End(xlDown))
  MyRange(MyRange.Cells.Count).Offset(1, 1).Formula = "=SUM(" & MyRange.Address & ")"
End With
Set MyRange = Nothing
End Sub

Ciao,
Holger

[Teeroy]

Hi Nugetti,

My understanding of your problem appears pretty similar to HaHoBe's so my code is similar but starts from the ActiveCell in column "D" rather than a defined position.

Sub teeroy()
With Range(ActiveCell, ActiveCell.End(xlDown))
.Cells(.Cells.Count, 1).Offset(0, 2).Formula = "=sum(" & .Address & ")"
End With
End Sub

[Nugetti]

Thank you HaHoBe and Teeroy. The problem is now solved

[snb]

2 more alternatives, provided cell F8 is the active cell:

Sub M_snb()
    ActiveCell = "=SUM(" & Application.InputBox("select the range", , , , , , , 8).Address & ")"
End Sub

Sub M_snb2()
    ActiveCell = "=sum(" & ActiveCell.CurrentRegion.Columns(1).Address & ")"
End Sub