So.. your script is simply inserting Excel formulas into the cells?
First of all, we need to discuss "relative" addresses vs "Absolute" addresses.
If you insert the formula:
In Cell F16, you inserted the formula:
"=CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C)"
In Excel, that resulted in:
=CORREL(B2:B15,F2:F15)
Now, if you "drag" the cell to G16, it becomes:
=CORREL(C2:C15,G2:G15)
That is because these formulas are taken to be "in relation to" (or "relative
to")
the current cell (or active Cell)
So, when you copy it one cell to the right, all cell references are moved one
cell
to the right.
To "anchor" a column or row, you use the "$" symbol.
So, changing F16 to:
=CORREL($B2:$B15,F2:F15)
will "anchor" the first array to the "B" column.
Dragging it to the right, gives G16 as:
=CORREL($B2:$B15,G2:G15)
The "B" column is "anchored", but the second array moves with the cell...
Now, back to your script.
You'll find that if you record a macro and edit F16, you'll get:
ActiveCell.FormulaR1C1 =
"=CORREL(R[-14]C2:R[-1]C2,R[-14]C:R[-1]C)"
compared to:
"=CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C)"
Notice your first "C[-4]" changed to "C2" ??
That is because the [] symbols represent the RELATIVE offset from the active
cell.
>From cell F16, R[-14]C[-4] means from Row 16, move 14 rows up, and column
>F(6),
move 4 columns left.
which puts you at cell B2
If you ALWAYS wanted to use the Column B, then change it to C2 (or Column 2)
does that make sense?
Paul
________________________________
From: Fred <jonathanepos...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tue, January 25, 2011 3:41:46 PM
Subject: $$Excel-Macros$$ keep one column constant while changes the second
I am trying to run a simple script the calculates the correlation
between column B and the column C, then does the same thing for column
B and column D, and then column B and column E, etc. I can't figure
out how to keep the first column (i.e. column B) constant, while
moving reference to the second column. Line 6 below seems to be the
problem.
1 Range("F16").Select
2 ActiveCell.FormulaR1C1 =
"=CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C)"
3 ActiveCell.Offset(0, 1).Select
4 ActiveCell.FormulaR1C1 =
"=CORREL(R[-14]C[-5]:R[-1]C[-5],R[-14]C:R[-1]C)"
5 ActiveCell.Offset(0, 1).Select
6 ActiveCell.FormulaR1C1 = "=CORREL("B2":"B15",R[-14]C:R[-1]C)"
Any suggestions??
Thanks
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
