Using a range object inside a formula

  • Thread starter Thread starter Café
  • Start date Start date
C

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.
 
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
 
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
 
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.
 
Back
Top