PDA

View Full Version : Set range and calculate sum problem



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!

patel
09-13-2013, 02:39 AM
Your goal is not clear for me, attach please a sample file with data and desired result

HaHoBe
09-13-2013, 03:27 AM
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
09-14-2013, 12:33 AM
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
09-15-2013, 10:51 PM
Thank you HaHoBe and Teeroy. The problem is now solved :)

snb
09-16-2013, 12:35 AM
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