Hopefully this is straightforward but the solution eludes me.
I have a single column of data (actually these are daily closing stock prices).
I am looking to produce a second column showing the maximum value of the r2 coefficient of determination using the excel LINEST function.
The problem is that I need to look at an expanding range of data values eg (a150:a1000), (a149:a1000), (a148:a1000), etc.
So for each step the range expands by 1 cell.
I can do this in excel using the "height" value in the "offset" command but this doesn't seem to exist in VBA.
How do I handle a range which expands by 1 cell each step?
I have tried using the "cell" property as in Range(cells(150,1),cells(1000,1)) but this doesn't help as the arguments cannot be variables.
Hopefully I'm missing the obvious.
Bookmarks