Hello, how would I go about this:

Range("F8").Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(2, 0).Select
Range("F10", Selection).Select
Dim rge As Range
Set rge = Selection
Range("C10").FormulaR1C1 = "=sum(" & rge & ")"

Thats the concept of what I want to do. How do I fix it? I want to do this
since the range is not always the same. The range selection and object is
good.

Range("C10").formular1c1 = "=SUM(R1C3:R200C3)"
Will give the result of SUM($C$1:$C$200)

Range("C10").formular1c1 = "=SUM(R[1]C[3]:R[200]C[3])"
Will give the result of SUM(C1:C200)

Your code will need to obtain the row and column of the last cell in the range
something like this :

nLastRow = rge.row+rge.rows.count-1
nLastCol = rge.column+rge.columns.count-1
Range("C10").formular1c1 =
"=SUM(R10C6:R"+cstr(nLastRow)+"C"+cstr(nLastCol)+" )"

This will set the sum in C10 to include the cells from F10 to the last cell
in the selected range



--
If the post is helpful, please consider donating something to an animal
charity on my behalf - and remember to click YES


"Café" wrote:

Hello, how would I go about this:

Range("F8").Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(2, 0).Select
Range("F10", Selection).Select
Dim rge As Range
Set rge = Selection
Range("C10").FormulaR1C1 = "=sum(" & rge & ")"

Thats the concept of what I want to do. How do I fix it? I want to do this
since the range is not always the same. The range selection and object is
good.

Cafe,

Here is an example of using the object module to create address references
for formulas.

Best,

Matthew Herbert

Set Wks = ThisWorkbook.ActiveSheet

With Wks
Set Rng = .Range("F8")
Set Rng = Range(Rng, Rng.End(xlToRight).Offset(2, 0))
.Range("C10").Formula = "=SUM(" & Rng.Address(External:=True) & ")"
End With

"Café" wrote:

Hello, how would I go about this:

Range("F8").Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(2, 0).Select
Range("F10", Selection).Select
Dim rge As Range
Set rge = Selection
Range("C10").FormulaR1C1 = "=sum(" & rge & ")"

Thats the concept of what I want to do. How do I fix it? I want to do this
since the range is not always the same. The range selection and object is
good.

Hi,

I'm a bit confused by the question but how about this for a start. All one
line

Range("C10").Formula = "=sum(" & Range("f8:" &
Range("f8").End(xlToRight).Address).Resize(3).Addr ess & ")"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Café" wrote:

Hello, how would I go about this:

Range("F8").Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(2, 0).Select
Range("F10", Selection).Select
Dim rge As Range
Set rge = Selection
Range("C10").FormulaR1C1 = "=sum(" & rge & ")"

Thats the concept of what I want to do. How do I fix it? I want to do this
since the range is not always the same. The range selection and object is
good.