More Linear Regression

[43fan]

Thanks to all of those who replied. Forecast will do exactly what I'm
looking for, but I'm having a tough time converting it into VBA.

I read values in from a text file, storing them in arrays. These values are
placed in cells on a worksheet as well.

If I use the worksheet function forecast(), in a worksheet, it looks
something like this:
FORECAST(0,$B$28:$B29,L$28:L29)
That's for the first value.
The next value would increase B29 to B30, and L29 to L30. Like this:
FORECAST(0,$B$28:$B30,L$28:L30)

When I go to insert this into my VBA programming though, I'm having a hard
time figuring out how to do it. I have the values I need both already on
the worksheet, as well as still in the array.

I tried doing something like this:
Worksheets(sheet).Cells(row, column) = Application.Forecast(0,
Worksheets(sheet).Cells(Row+3,21):Worksheets(sheet).Cells(Row+3,j),
Val(.ArrayVal(j)))

But it doesn't like this at all.

Help?????

 

[Tom Ogilvy]

Worksheets(sheet).Cells(row, column) = Application.Forecast(0,
Worksheets(sheet).Cells(Row+3,21):Worksheets(sheet).Cells(Row+3,j),
Val(.ArrayVal(j)))

set rng1 = Range("B28:B29")
set rng2 = Range("L28:L29")

rw = 100
for i = 0 to 9

cells(rw,i) = application.Forcast(0,rng1.Resize(rng1.rows + i,1),
rng2.Resize(rng2.rows + i,1))

Next i

 

[43fan]

Worksheets(sheet).Cells(row, column) = Application.Forecast(0,
Worksheets(sheet).Cells(Row+3,21):Worksheets(sheet).Cells(Row+3,j),
Val(.ArrayVal(j)))

set rng1 = Range("B28:B29")
set rng2 = Range("L28:L29")

rw = 100
for i = 0 to 9

cells(rw,i) = application.Forcast(0,rng1.Resize(rng1.rows + i,1),
rng2.Resize(rng2.rows + i,1))

Next i

Tom,

Thanks, but still having some problems. It gives me an "invalid qualifier"
at the
rng1.rows with "rng1" highlighted.

Also, I'll be doing a variable number of points, a variable number of times.

IOW, I'm looping through for the number of intervals/data points, multiple
times, based on the number of data sets that I have. I suppose the starting
point will always be the same, but I'm wondering if there's a way to make
that variable as well?

 

[43fan]

Tom,

Thanks, but still having some problems. It gives me an "invalid qualifier"
at the
rng1.rows with "rng1" highlighted.

Ok, got rid of this problem by dim'ing rng1 and rng2 as Range, but now I get
a type mismatch...

 

[Tom Ogilvy]

Adding the count should clear up the type mismatch.


Dim rng1 as Range
Dim rng2 as Range
Dim rw as Long, i as Long
set rng1 = Range("B28:B29")
set rng2 = Range("L28:L29")

rw = 100
for i = 0 to 9

cells(rw,i) = application.Forcast(0,rng1.Resize(rng1.rows.count + i,1),
rng2.Resize(rng2.rows.count + i,1))

Next i