dimensioning variables

[Carla101]

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.

 

[NickHK]

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]

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

 

[NickHK]

Carla,
Code works here, Office2K

NickHK

 

[Bob Phillips]

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]

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

 

[NickHK]

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]

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.

 

[Peter T]

Maybe something like

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

Set rng = Range("A11")
Set rng = rng.Resize(2, rng.Columns.Count) ' A12

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

End Sub

Regards,
Peter T