CODEWITHSUNDEEP
vbaexcelexcel-formula
Argyris
Argyris

Reputation: 53

VBA Insert formula with offset in cell and loop

I've got hourly data. I need to have a cell that computes say the R2 for days 1 and 7 between two different variables (say column A and B) and then loop this so next time it computes the R2 for days 2-8 and then for days 3-9 etc etc. I must say that the cell that has the R2 formula is used in Solver to solve some code/formulas for max R2, so the cell that contains the R2 needs to be a formula and not a value.. (I have been able to offset this as a value but not as a formula). Part of the current code is the following:

i = 0
ActiveSheet.Cells(24,13).Value = Application.WorksheetFunction.RSq(RangeA.offset(i,0), RangeB.offset(i,0)

i = i + 24

Many many thanks in advance for any helpful tips

Cheers

Upvotes: 1

Views: 3544

Answers (1)

ThunderFrame
ThunderFrame

Reputation: 9461

Assuming you have named ranges RangeA and RangeB, you could use:

ActiveSheet.Cells(24,13).Formula =  "=RSQ(RangeA,RangeB)"

Or, you can use relative references in R1C1 notation like so:

ActiveSheet.Cells(24,13).Formula = "=RSQ(RC[-2],RC[-1])"

or, for multi-celled ranges:

ActiveSheet.Cells(24,13).Formula = "=RSQ(RC[-2]:R[2]C[-2],RC[-1]:R[2]C[-1])"

Upvotes: 1

Related Questions