I am in the process of beginning to teach myself VBA for Excel 2007. The
primer I am using starts with recording macros, editing recorded macros, and
eventually moves on to writing code from scratch.
Question, I recorded a macro where I put a formula =sum(A:A) into cell C1.
The recorder coded this formula to
Range("C1").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-2])"
I am not sure I understand the syntax of the sum function. I do believe it
is indicating to select data 2 columns tot he left of column C, but does it
assume the data populates all cells that column, in this example column A.

yes

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Marsh" <(E-Mail Removed)> wrote in message
news:E5200A46-C43B-44EE-B231-(E-Mail Removed)...
>I am in the process of beginning to teach myself VBA for Excel 2007. The
> primer I am using starts with recording macros, editing recorded macros,
> and
> eventually moves on to writing code from scratch.
> Question, I recorded a macro where I put a formula =sum(A:A) into cell C1.
> The recorder coded this formula to
> Range("C1").Select
> ActiveCell.FormulaR1C1 = "=SUM(C[-2])"
> I am not sure I understand the syntax of the sum function. I do believe
> it
> is indicating to select data 2 columns tot he left of column C, but does
> it
> assume the data populates all cells that column, in this example column A.

the formula for this
"=SUM(C[-2])"
will be changed to =SUM(A:A)
when you run the code.


but that depends where your cell is 'active' as you are in the cell in
colume C, because Range("C1") is just selected then the C[-2] will point to
Column A:A , if you put C[+2] then the formula will be "=SUM(E:E)" .

if you don't want the formula depend on the location then you just simply
put =sum(A:A), this case won't depend on the active cell.


another point is that, sometime selecting the cell is not necessary. For
example

Range("G1").select
selection.formula = "=$A$10"

then you can simply it to:
Range("G1").formula = "=$A$10"

the this new line of code won't move the active cell at all and sometims
simplying the code is the first step of learning from macro to VBA coding.






"Marsh" wrote:

> I am in the process of beginning to teach myself VBA for Excel 2007. The
> primer I am using starts with recording macros, editing recorded macros, and
> eventually moves on to writing code from scratch.
> Question, I recorded a macro where I put a formula =sum(A:A) into cell C1.
> The recorder coded this formula to
> Range("C1").Select
> ActiveCell.FormulaR1C1 = "=SUM(C[-2])"
> I am not sure I understand the syntax of the sum function. I do believe it
> is indicating to select data 2 columns tot he left of column C, but does it
> assume the data populates all cells that column, in this example column A.

You can manually create formulas in A1 reference style or R1C1 reference style.

In xl2003 menus, you can toggle this setting on the:
tools|Options|General tab

If you create a formula using A1 reference style:
=sum(a:a)
In cell C1

and then switch to R1C1 reference style, you'll see numbers instead of letters
at the top of the columns. (This may be confusing, so remember how to toggle
this setting.)

But the formula changes to:
=SUM(C[-2])

C represents the Column. The stuff in [] indicates where (in relationship to
the cell with the formula) to find this column.

In this case, it says to go 2 columns to the left (column A in A1 reference
style).

It may be better to use single cells in a couple of test formulas.

Try putting:
=A1+A2 in B1
=$a1+$a2 in B2
=a$1+a$2 in B3
=$a$1+$a$2 in B4
(while in A1 reference style)

Then switch to R1C1 reference style and take a look at each of those cells with
the formulas:

You'll see:
=RC[-1]+R[1]C[-1]
RC[-1] means same row and one column to the left

=R[-1]C1+RC1
R[-1]C means one row up (positives are down) and column 1 (always column 1)

=R1C[-1]+R2C[-1]
R1C[-1] means row 1 (always) and the column to the left

=R1C1+R2C1
R1C1 means Row 1 and column 1 (A1) and R2C1 means row 2 and column 1 (A2)

For me, I rarely use R1C1 reference style when I'm doing things manually. But
there are times in code where populating a formula in a range of cells is much,
much easier using .formulaR1C1.

And just because you use .formula or .formular1c1 in code doesn't mean it
changes the way the user sees the formula--that's still specified by the option
they choose.

One of the nice features of using R1C1 is when you want to check the consistency
of your formulas in a single column.

Turn on R1C1 reference style.
View formulas (Tools|Options|view tab)
and every (consistent) formula in that range will look identical.

You'll be able to pick out the cell(s) with the formulas that have had some
tweaking done to them pretty easily.

As for the formula:
(in A1 reference style)
=sum($a:$a) or =sum(a:a)

or (in R1C1 reference style)
=sum(c1) or =sum(c[-2]) (only in column 3/C)

They all work against the used range. If you've only filled up rows 1-1000,
then excel knows what to look at. But by using this formula, you don't have to
adjust the formula when you add data to row 1001.




Marsh wrote:
>
> I am in the process of beginning to teach myself VBA for Excel 2007. The
> primer I am using starts with recording macros, editing recorded macros, and
> eventually moves on to writing code from scratch.
> Question, I recorded a macro where I put a formula =sum(A:A) into cell C1.
> The recorder coded this formula to
> Range("C1").Select
> ActiveCell.FormulaR1C1 = "=SUM(C[-2])"
> I am not sure I understand the syntax of the sum function. I do believe it
> is indicating to select data 2 columns tot he left of column C, but does it
> assume the data populates all cells that column, in this example column A.

--

Dave Peterson

Adding to the others. Spend an hour or two to get somewhat familiar with the
R1C1 notation style, it is very essential for VBA programming in Excel. For
example, this line

ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

will enter a sum formula, summing from row 1 same column to the cell just
above the formula cell, no matter which cell that is active. To do a thing
like this in A1 style you must first know the the column letter of the cell,
row number, ...

HTH. Best wishes Harald