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

NoSparks · #2 07-28-2017, 03:53 PM

Depending on how the data is loaded into columns A&B, I'd consider using an Excel table, then you could just refer directly to the table columns.

A page on table stuff... https://www.thespreadsheetguru.com/b...t-excel-tables

NoSparks · #3 07-28-2017, 08:25 PM

OR

Code:

Sub Test()
 Dim Y
 Dim X
 Dim Arr1
 Dim Arr2
 
 Dim lr As Long
 
 lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 
 With Application
     'Y = .Transpose([a1:a10])
     'X = .Transpose([b1:b10])
     
     Y = .Transpose(Range("A1:A" & lr).Value)
     X = .Transpose(Range("B1:B" & lr).Value)
     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

SiO2 · #4 07-31-2017, 12:47 AM

Hello Nosparks.

The second option is working.

Fantastic. Thanks a lot.

SiO2

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