Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Better Understanding

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.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 119
Default Better Understanding

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.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Better Understanding

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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 449
Default Better Understanding

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Understanding the difference Fabian Excel Programming 3 June 19th 09 08:01 PM
Understanding SUMPRODUCT Jordan Excel Worksheet Functions 11 May 25th 06 11:08 PM
Not understanding with/end with davegb Excel Programming 7 March 6th 06 10:42 PM
Not understanding If Not..Then nothing davegb Excel Programming 6 June 14th 05 04:49 PM
Understanding Templates Syed Zeeshan Haider Excel Programming 6 May 25th 05 12:39 AM


All times are GMT +1. The time now is 12:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"