VBA use variable to calculate RSQ

[Jacobs22]

Hi,

i am trying to calculate the R squared of 2 ranges. i have created 2 arrays with my data, Ary and Ary2 and am trying to calcuate RSQ of those 2 ranges.

i have been trying to do so with a formula buy have not had any luck getting the right verbage. or i may be going about this in the wrong way.

i have been trying the following

ws.Cells(k, 29).FormulaR1C1 = "=RSQ(Ary, Ary2)" also tried as ws.Cells(k, 29).FormulaR1C1 = "=RSQ(" & Ary & "," & Ary2 & ")"

i wasnt sure if i am just not getting the code correct, or if something is wrong with the array or this approach in general. the variable k is used to loop through the same procedure on multiple rowns.

 

[Tetra201]

What are Ary and Ary2?

If they are declared as ranges, try

ws.Cells(k, 29).Formula = "=RSQ(" & Ary.Address & "," & Ary2.Address & ")"

If they are declared as arrays, try

ws.Cells(k, 29) = WorksheetFunction.RSq(Ary, Ary2)

 

[Jacobs22]

they were declared as arrays. the 2nd option from tetra worked! Thanks!