Change a static range by a dynamic one

SiO2 · #1 07-28-2017, 11:47 AM

Hi,

I found on internet the following code to calculate the coefficients of a polynominal regression line. This is a static range with 10 elements.

How can I make the range dynamic (for more or less elements).
I try to replace [a1:a10] by a dimensioned range, and [b1:b10] by another range, but this results in an error.
Does anyone knows an answer. Thanks in advance.
SiO2

Code:

Sub Test()
 Dim Y 
 Dim X
 Dim Arr1
 Dim Arr2
 With Application  Y = .Transpose([a1:a10])
 X = .Transpose([b1:b10])
 Arr1 = .Power(.Transpose(X), Array(1, 2, 3))
 Arr2 = .LinEst(Y, .Transpose(Arr1))
End With
 MsgBox "coefficients are " & Chr(10) & Join(Arr2, Chr(10))
 End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
change the definition of range name	cspitser	Excel	2	03-29-2017 01:30 PM
Copy dynamic range between workbooks	elieprolb	Excel Programming	4	02-06-2017 12:40 AM
Workarounds for the find "dynamic" range bug in VBA?	Robert K S	Word VBA	2	11-07-2016 01:00 PM
Dynamic font color change	Critcheeees	PowerPoint	2	01-27-2015 02:45 AM
Multiple Dynamic Worksheets Consolidated into One Static Worksheet	mars1886	Excel Programming	3	02-09-2014 12:50 AM