Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better Understanding
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Understanding the difference | Excel Programming | |||
Understanding SUMPRODUCT | Excel Worksheet Functions | |||
Not understanding with/end with | Excel Programming | |||
Not understanding If Not..Then nothing | Excel Programming | |||
Understanding Templates | Excel Programming |