Range Syntax

[bishoposiris]

What is the correct syntax to make this line of code work:

Please Login or Register  to view this content.

CtrLastColumn is defined as

Please Login or Register  to view this content.

As it is it's giving me an Application-defined or Object-defined error. It is in a With Sheets block.

[Norie]

Range expects the column letter, not number.

Try this for the range.

Please Login or Register  to view this content.

[MarvinP]

Hi bishoposiris,

I like to use cells in my range instead of letters...

Say you want to sum from B3 to D7. I like to write this as:
Range(Cells(3,"B"),Cells(7,"D"))
See if that works for you.

Find all the different ways to use Range at:
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

[Izandol]

.FormulaR1C1 will accept a value, which will be returned by WorksheetFunction.Sum.

I do not know if OP actually wishes a formula in the cells however.

[bishoposiris]

That's a good point Izandol. The cell has a value in it not a formula. I need to copy the formula all the way down this column (to the last row in the range). How do I do this?

[Izandol]

If you know last row:

Please Login or Register  to view this content.

Note you may not include CtrLastColumn in SUM or you will have circular reference!

[bishoposiris]

Great! Works as intended. Can you explain how =Sum(RC2:RC[-1]) works? What does RC mean?

[alansidman]

Look at this link to understand R1C1 and A1 convention differences

http://office.microsoft.com/en-us/ex...005198323.aspx

[Izandol]

R = Row
C = Column
if you see number in [] it is relative to formula column: C[-1] means one column to left of formula cell. For same row or column you may use only R or C, or R[0] or C[0]

So RC2 means same row, column 2 (column B) and RC[-1] means same row, one column to left of formula cell.

[bishoposiris]

Great explanation. Thank you!