About Us

Martin Brown

On 27/07/2011 19:45, wrote:
I'm trying to create a VBA macro that calculates the coefficients/T-stats and R-square for each variable with in a regression. The trick is that I have 10 candidate independent variables and I'd like to be able to test each combination of these variables.

I created some code that used fixed ranges for the Linest calc and that worked.
I got some help creating code that selected each unique combination.
But when I put these together - I get a runtime error 1004 on the linest.
I know that it has something to do with how I'm assigning the unique combination to a range that will be used in the Linest calculation.

I would appreciate any thoughts on how to correct this.

Regards - Marston

Here is the code below:

Option Base 1
Option Explicit
Sub combinKofN()
Dim rngs
Dim nRngs As Long, maxCombin As Long, nCombin As Long
Dim nSelect As Long, i As Long, j As Long
Dim r As String
Dim xRng As Range
Dim yRng As Range
Dim v
Dim k As Integer

rngs = Array("A2:A112", "B2:B112", "C2:C112", "D2:D112", "E2:E112", "F2:F112", "G2:G112", "H2:H112", "I2:I112", "J2:J112")
Set yRng = Range("K2:K112")

Linest isn't the worlds most numerically stable algorithm - although
XL2007 implementation is somewhat better than earlier versions. You
should be aware that the answer it gives need not be entirely accurate.

You would be well advised to take a long hard look at the matrix of
possible dot products of your trial vectors of the form A.B/(|A|*|B|)

If any of these scalars are close to unity then there is a potential
risk of numerical instability problems when they are both used.

Regards,
Martin Brown

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
runtime error '1004' application or object defined error	Janis	Excel Programming	4	November 18th 09 03:01 PM
runtime error '1004' application or object defined error. Please help	deej	Excel Programming	0	August 1st 07 09:26 AM
Runtime error 1004	Zippy	Excel Programming	3	March 2nd 06 08:39 PM
runtime error 1004	valdesd	Excel Discussion (Misc queries)	2	October 12th 05 03:20 PM
Excel 2003 Macro Error - Runtime error 1004	Cow	Excel Discussion (Misc queries)	2	June 7th 05 01:40 PM

Menu

About Us