| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
| Range Naming, dimensioning | New Users to Excel | |||
| Variables | Excel Programming | |||
| dimensioning across modules | Excel Programming | |||
| Dimensioning Arrays | Excel Programming | |||
Linear Mode
