how to change range in VBA?
 

I use the following code to calculate correlation
coefficient and write it to a cell:
--------------------------------------------
Sheets("sheet1").Cells(4, 3) = "=correl
(R1C1:R5C1,R1C2:R5C2)"
--------------------------------------------
However, I would like to change the cell references
programmatically. For example,next cell reference
(R1C3:R5C4, R1C5:R5C5)and so on.

How can I do that?

how to change range in VBA?
 

Nazrul

You can use the Address property of the range object to build your string

....Cells(4,3).Formula = "=correl(" & Range("C1:D5").Address & "," &
Range("E1:E5").Address & ")"

I'm not good at R1C1 notation, so you'll have to convert that yourself. I
believe you need the FormulaR1C1 property instead of just formula.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Nazrul" wrote in message
...
I use the following code to calculate correlation
coefficient and write it to a cell:
--------------------------------------------
Sheets("sheet1").Cells(4, 3) = "=correl
(R1C1:R5C1,R1C2:R5C2)"
--------------------------------------------
However, I would like to change the cell references
programmatically. For example,next cell reference
(R1C3:R5C4, R1C5:R5C5)and so on.

How can I do that?

how to change range in VBA?
 

Nazrul,

as the row and column values are numbers, if you can define a pattern (or
even if you can't) you can assign each R and C value a variable and then
alter the variable.

Steve
"Nazrul" wrote in message
...
I use the following code to calculate correlation
coefficient and write it to a cell:
--------------------------------------------
Sheets("sheet1").Cells(4, 3) = "=correl
(R1C1:R5C1,R1C2:R5C2)"
--------------------------------------------
However, I would like to change the cell references
programmatically. For example,next cell reference
(R1C3:R5C4, R1C5:R5C5)and so on.

How can I do that?