dimensioning variables
 

One of my variables is the range D6:S1047 but an object mismatch occurs
when I dimension it as a range. The macro works fine with a smaller
range such as D6:S266. Does anybody know how to declare such a large
range as a variable?
Thanks.


--
Carla101
------------------------------------------------------------------------
Carla101's Profile: http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057

dimensioning variables
 

Carla,
You didn't post your code, but is it like this ?
Dim r As Range
Set r = ActiveSheet.Range("D6:S1047 ")
MsgBox MsgBox r.Rows.Count & " x " & r.Columns.Count

NickHK

"Carla101" wrote in
message ...

One of my variables is the range D6:S1047 but an object mismatch occurs
when I dimension it as a range. The macro works fine with a smaller
range such as D6:S266. Does anybody know how to declare such a large
range as a variable?
Thanks.


--
Carla101
------------------------------------------------------------------------
Carla101's Profile:
http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057

dimensioning variables
 

My code is as follows:

Dim XRange As Range
Dim YRange As Range

Set XRange = Sheets("SEK").Range("D6:S1047")
Set YRange = Sheets("SEK").Range("B6:B1047")


--
Carla101
------------------------------------------------------------------------
Carla101's Profile: http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057

dimensioning variables
 

Carla,
Code works here, Office2K

NickHK

"Carla101" wrote in
message ...

My code is as follows:

Dim XRange As Range
Dim YRange As Range

Set XRange = Sheets("SEK").Range("D6:S1047")
Set YRange = Sheets("SEK").Range("B6:B1047")


--
Carla101
------------------------------------------------------------------------
Carla101's Profile:
http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057

dimensioning variables
 

I too can dimension a range and assign it D6:S1047 without any problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Carla101" wrote in
message ...

One of my variables is the range D6:S1047 but an object mismatch occurs
when I dimension it as a range. The macro works fine with a smaller
range such as D6:S266. Does anybody know how to declare such a large
range as a variable?
Thanks.


--
Carla101
------------------------------------------------------------------------
Carla101's Profile:
http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057

dimensioning variables
 

I don't know what the problem is then. What I'm trying to do is a
multiple regression with a rolling window. My "y" values go down column
B for about 10 years worth of data, and my "x" values cover columns D to
S also for 10 years of data. The macro works for a 1year rolling window
which means rolling the ranges D6:S266 and B6:B266 down through the 10
years of data. But when I try and do a 4year rolling window for the
ranges D6:S1047 and B6:B1047 I get a type mismatch error on line:

Range("T6").Offset(i, 0).Value = MyLinestOut(2, 1)

My full code is as follows:

Dim i
i = 0

Dim XRange As Range
Dim YRange As Range

Set XRange = Sheets("SEK").Range("D6:S1047")
Set YRange = Sheets("SEK").Range("B6:B1047")

Dim X
Dim Y
Dim MyLinestOut

Do Until i = 1561
X = XRange.Offset(i, 0).Value
Y = YRange.Offset(i, 0).Value

MyLinestOut =
Application.MMult(Application.MMult(Application.MI nverse(Application.MMult(Application.Transpose(X),
X)), Application.Transpose(X)), Y)
Range("T6").Offset(i, 0).Value = MyLinestOut(2, 1)
Range("U6").Offset(i, 0).Value = MyLinestOut(3, 1)
Range("V6").Offset(i, 0).Value = MyLinestOut(4, 1)
Range("W6").Offset(i, 0).Value = MyLinestOut(5, 1)
Range("X6").Offset(i, 0).Value = MyLinestOut(6, 1)
Range("Y6").Offset(i, 0).Value = MyLinestOut(7, 1)
Range("Z6").Offset(i, 0).Value = MyLinestOut(8, 1)
Range("AA6").Offset(i, 0).Value = MyLinestOut(9, 1)
Range("AB6").Offset(i, 0).Value = MyLinestOut(10, 1)
Range("AC6").Offset(i, 0).Value = MyLinestOut(11, 1)
Range("AD6").Offset(i, 0).Value = MyLinestOut(12, 1)
Range("AE6").Offset(i, 0).Value = MyLinestOut(13, 1)
Range("AF6").Offset(i, 0).Value = MyLinestOut(14, 1)
Range("AG6").Offset(i, 0).Value = MyLinestOut(15, 1)
Range("AH6").Offset(i, 0).Value = MyLinestOut(16, 1)
Range("AI6").Offset(i, 0).Value = MyLinestOut(1, 1)

i = i + 1
Loop


--
Carla101
------------------------------------------------------------------------
Carla101's Profile: http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057

dimensioning variables
 

Carla,
Your problem is not with setting the ranges.
I'm not that clear what you are trying to do, but if you check UBound(X) (or
Y), you will see that it does not change.
Also, I suspect you really want .Resize instaed of Offset.

I don't use these matrix functions or Transpose (note
Application.WorksheetFunction.MMULT), but I would imagine you troubles lie
there.

NickHK

"Carla101" wrote in
message ...

I don't know what the problem is then. What I'm trying to do is a
multiple regression with a rolling window. My "y" values go down column
B for about 10 years worth of data, and my "x" values cover columns D to
S also for 10 years of data. The macro works for a 1year rolling window
which means rolling the ranges D6:S266 and B6:B266 down through the 10
years of data. But when I try and do a 4year rolling window for the
ranges D6:S1047 and B6:B1047 I get a type mismatch error on line:

Range("T6").Offset(i, 0).Value = MyLinestOut(2, 1)

My full code is as follows:

Dim i
i = 0

Dim XRange As Range
Dim YRange As Range

Set XRange = Sheets("SEK").Range("D6:S1047")
Set YRange = Sheets("SEK").Range("B6:B1047")

Dim X
Dim Y
Dim MyLinestOut

Do Until i = 1561
X = XRange.Offset(i, 0).Value
Y = YRange.Offset(i, 0).Value

MyLinestOut =

Application.MMult(Application.MMult(Application.MI nverse(Application.MMult(A
pplication.Transpose(X),
X)), Application.Transpose(X)), Y)
Range("T6").Offset(i, 0).Value = MyLinestOut(2, 1)
Range("U6").Offset(i, 0).Value = MyLinestOut(3, 1)
Range("V6").Offset(i, 0).Value = MyLinestOut(4, 1)
Range("W6").Offset(i, 0).Value = MyLinestOut(5, 1)
Range("X6").Offset(i, 0).Value = MyLinestOut(6, 1)
Range("Y6").Offset(i, 0).Value = MyLinestOut(7, 1)
Range("Z6").Offset(i, 0).Value = MyLinestOut(8, 1)
Range("AA6").Offset(i, 0).Value = MyLinestOut(9, 1)
Range("AB6").Offset(i, 0).Value = MyLinestOut(10, 1)
Range("AC6").Offset(i, 0).Value = MyLinestOut(11, 1)
Range("AD6").Offset(i, 0).Value = MyLinestOut(12, 1)
Range("AE6").Offset(i, 0).Value = MyLinestOut(13, 1)
Range("AF6").Offset(i, 0).Value = MyLinestOut(14, 1)
Range("AG6").Offset(i, 0).Value = MyLinestOut(15, 1)
Range("AH6").Offset(i, 0).Value = MyLinestOut(16, 1)
Range("AI6").Offset(i, 0).Value = MyLinestOut(1, 1)

i = i + 1
Loop


--
Carla101
------------------------------------------------------------------------
Carla101's Profile:
http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057

dimensioning variables
 

Well, I don't actually want the size of the range to change.
I just want the range to shift down the rows, but always covering the
same 'n' number of rows. Anyway, I'll look further into it, thanks for
your help.


--
Carla101
------------------------------------------------------------------------
Carla101's Profile: http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057

dimensioning variables
 

Maybe something like

Sub test2()
Dim i as long
Dim rng As Range

Set rng = Range("A1:D1")
Set rng = rng.Resize(2, rng.Columns.Count) ' A1:D2

For i = 3 To 12 Step 3
rng.Offset(i, 0).Value = i
Next

End Sub

Regards,
Peter T

"Carla101" wrote in
message ...

Well, I don't actually want the size of the range to change.
I just want the range to shift down the rows, but always covering the
same 'n' number of rows. Anyway, I'll look further into it, thanks for
your help.


--
Carla101
------------------------------------------------------------------------
Carla101's Profile:
http://www.excelforum.com/member.php...o&userid=30346
View this thread: http://www.excelforum.com/showthread...hreadid=500057