Automatically Referencing data ranges in =linest( )

[groovymoon]

I have a rather large data file. It has 11 columns and 3000 (sometimes more, sometimes less) rows of data. I perform the =linest() function on 2 columns of a contiguous subset of this data. This subset is defined by an upper and lower boundary on one of the columns of data. Normally I hand pick the subset and then hand edit =linest(). This can be quite time consuming and I want to automate the process using either a VBA macro or an excel formula or both. I have made some progress by using =address() and =match() to find the beginning and ending cell addresses of the "known x's" and "known y's" I want to perform the linest function on.

To find the beginning and ending cell addresses of the x's and y's my formula looks like this

=ADDRESS(MATCH($G$3,D$18:D$5007,-1)+ROW(D$18:D$5007),1,4,1)
(This one finds the upper boundary of the "known x's")

Where:
$G$3 is one of the boundary conditions (the upper in this case)
D$18:D$5007 is a range of cells where the data which is considered for the boundaries is.

There are three more similar formulas to find the other 3 boundaries

When I try to insert these into linest() in the form
=linest( formula1:formula2,formula3:formula4)
I get the #value error. I have looked at these hard and there is no syntax style of error.

I have also tried to record a macro that:
1) Copies the boundry addresses and pastes them as values to another set of cells
2) Concactenates the addresses so they look like this, i.e.: H1070:H3013,A1070:A3013
3) Copies the results of step 2 into a new cell as "value" (so they are text)
4) Then attempts to edit the cell, copy the text, move to a different cell
5) types +linest(
6) Paste the address range and then type the close parenthesis and insert the complete funcion into the new cell.

Problem with this approach is that in the VBA code it copies the cell addresses from the data from the worksheet that was used to record the macro into the VBA code and then places that into =linest(). so the code looks like this:

Range("H13").Select
    Application.CutCopyMode = xlCopy
    ActiveCell.FormulaR1C1 = "H1288:H3200,A1288:A3200"
    Range("H15").Select
    ActiveCell.FormulaR1C1 = _
        "=+LINEST(R[1273]C:R[3185]C,R[1273]C[-7]:R[3185]C[-7])"

Then if the macro is run on a different set of data... the wrong ranges of data are inserted into =linest()

I attached the file. It is actually a *.rar file. I had to change the extension to make it ok to upload.

Any help would be greatly appreciated... Thanks

[romperstomper]

Assuming the two ranges are in the same rows, then you only really need two formulas - to get the start and end rows - using MATCH. Then use those in INDEX formulas:
=linest(index($A$18:$A$5007,startrow):index($A$18:$A$5007,endrow),index($B$18:$B$5007,startrow):index($B$18:$B$5007,endrow))
for example.

[groovymoon]

THANK YOU THANK YOU....
Ihave been working with excel for years but never used macros or more complicated formulas to get the job done... So I am a little grren... thqanks for all your help Your idea worked perfectly (with just one little tweak)