Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 07-28-2017, 11:47 AM
SiO2 SiO2 is offline Change a static range by a dynamic one Windows 7 64bit Change a static range by a dynamic one Office 2013
Novice
Change a static range by a dynamic one
 
Join Date: Jul 2017
Posts: 4
SiO2 is on a distinguished road
Default Change a static range by a dynamic one

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
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Change a static range by a dynamic one 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
Change a static range by a dynamic one Workarounds for the find "dynamic" range bug in VBA? Robert K S Word VBA 2 11-07-2016 01:00 PM
Change a static range by a dynamic one Dynamic font color change Critcheeees PowerPoint 2 01-27-2015 02:45 AM
Change a static range by a dynamic one Multiple Dynamic Worksheets Consolidated into One Static Worksheet mars1886 Excel Programming 3 02-09-2014 12:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft