Nugetti
09-13-2013, 12:38 AM
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.
10581
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!
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.
10581
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!