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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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")

Carla,
Code works here, Office2K

NickHK

"Carla101" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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.MInverse(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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.MInverse(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
>

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>