Expanding Range Problem

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.

Originally Posted by horneblower

.... as the arguments cannot be variables.

Are you saying you do NOT want to use variables or Cells() can not be used with variables?

Cells() can be used with variables and that is how i would do the task you are describing via a loop.

You can also do this without VBA.

• Put in your first formula using LINEST()
• Highlight the first cell reference and make it absolute (press F4)
• You should have something like LINEST($J$10:J13,K10:K13,,FALSE)
• Now when you copy down the formula, the first cell reference is fixed but the end cell of the range expands

isskint,

Thanks for your reply.

I can, and want to, use a variable to step through from say (a150:a160) to (a150:a1500).

I tried using cells but kept getting an error message saying I can't do that. Perhaps I got the syntax wrong. I'll try again.

I have done it in excel in the manor you describe. It's just that each cell looks for the max of about 1,000 linest calculations and there are about 5,000 cells to do this on...and about 200 different stocks to do THAT on so we're talking about a lot of calculations. I was hoping to automate it a bit.

Thanks again

horneblower