Using a range object inside a formula

[Café]

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.

 

[Paul]

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

 

[Matthew Herbert]

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

 

[Mike H]

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).Address & ")"
--
Mike

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