Using a range object inside a formula
 

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.

Using a range object inside a formula
 

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.

Using a range object inside a formula
 

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.

Using a range object inside a formula
 

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.