Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing select ranges in VBA
Hello:
I have a routine that asks the user to select two ranges (using application.input box). One of the ranges is a column vector rngY and the second is a matrix or column vector rngX. The two regions are required to have the same number of rows and the matrix must be contiguous. These two are passed to linest() function in Excel as: Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True) Everything works fine and I get the expected results. Now I want to change the result so that only the first n rows are used. Something like: Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5), rngX.rows(1 to 5), True, True) where the actual number of rows in rngY and rngX might be 10. ie I want to use the first half the data to run the regression. I know that for columns I can use: Result = Application.WorksheetFunction.LinEst(rngY.Columns( 1), rngX.Columns(2), True, True) Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved I could something like (which would allow me to select any subset of rows): Startrow = 1 Endrow = 5 For i = 1 To col For j = Startrow To endrow Xstore(j, i) = rngX(j, i) Next j Next i For j = Startrow To endrow Ystore(j) - rngY(J) next j Then Xstore and Ystore would contain the right data but this creates two problems, it is slow and the linest function expect ranges. Can someone suggest a strategy? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing select ranges in VBA
On Jul 25, 6:21*pm, Pieter wrote:
Typos (sorry): Change: Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved To: Which will correlate the Y variable with 2nd X variable. I would also like to be able to do the same thing for multiple columns which is also a problem I have not solved Change Ystore(j) - rngY(J) to: Ystore(j) = rngY(J) Pieter Hello: I have a routine that asks the user to select two ranges (using application.input box). One of the ranges is a column vector rngY and the second is a matrix or *column vector rngX. The two regions are required to have the same number of rows and the matrix must be contiguous. These two are passed to linest() function in Excel as: *Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True) Everything works fine and I get the expected results. Now I want to change the result so that only the first n rows are used. Something like: Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5), rngX.rows(1 to 5), True, True) where the actual number of rows in rngY and rngX might be 10. ie I want to use the first half the data to run the regression. I know that for columns I can use: Result = Application.WorksheetFunction.LinEst(rngY.Columns( 1), rngX.Columns(2), True, True) Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved I could something like (which would allow me to select any subset of rows): Startrow = 1 Endrow = 5 For i = 1 To col For j = Startrow To endrow Xstore(j, i) = rngX(j, i) Next j Next i For j = Startrow To endrow Ystore(j) - rngY(J) next j Then Xstore and Ystore would contain the right data but this creates two problems, it is slow and the linest function expect ranges. Can someone suggest a strategy? Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing select ranges in VBA
I've not used the LinEst function before, so I'm guessing here, but it
sounds like what you want to use is the Resize property of the range. See if this does what you want... Result = Application.WorksheetFunction.LinEst(rngY.Resize(5 ), rngX.Resize(5), True, True) Now, that resize takes place from the first row. If instead of the first 5 rows, you wanted the middle 5 rows (from a group of 11 rows total for example), then you would use the Resize property in conjunction with the Offset property... Result = Application.WorksheetFunction.LinEst(rngY.Offset(4 ).Resize(5), rngX.Offset(4).Resize(5), True, True) Check out the Resize and Offset properties (of the Range object) in the help files to see about handling the columns via the optional 2nd argument for them. -- Rick (MVP - Excel) "Pieter" wrote in message ... On Jul 25, 6:21 pm, Pieter wrote: Typos (sorry): Change: Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved To: Which will correlate the Y variable with 2nd X variable. I would also like to be able to do the same thing for multiple columns which is also a problem I have not solved Change Ystore(j) - rngY(J) to: Ystore(j) = rngY(J) Pieter Hello: I have a routine that asks the user to select two ranges (using application.input box). One of the ranges is a column vector rngY and the second is a matrix or column vector rngX. The two regions are required to have the same number of rows and the matrix must be contiguous. These two are passed to linest() function in Excel as: Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True) Everything works fine and I get the expected results. Now I want to change the result so that only the first n rows are used. Something like: Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5), rngX.rows(1 to 5), True, True) where the actual number of rows in rngY and rngX might be 10. ie I want to use the first half the data to run the regression. I know that for columns I can use: Result = Application.WorksheetFunction.LinEst(rngY.Columns( 1), rngX.Columns(2), True, True) Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved I could something like (which would allow me to select any subset of rows): Startrow = 1 Endrow = 5 For i = 1 To col For j = Startrow To endrow Xstore(j, i) = rngX(j, i) Next j Next i For j = Startrow To endrow Ystore(j) - rngY(J) next j Then Xstore and Ystore would contain the right data but this creates two problems, it is slow and the linest function expect ranges. Can someone suggest a strategy? Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing select ranges in VBA
Hello Rick:
Your suggestion was exactly what I was looking for. It works beautifully for the row problem. Thank you. I have not tried the column solution yet. I think the reisze option only works at the start or the end of a block of columns (or rows). I need to be able to delete columns from the middle and have the range "resized in the middle." For example if the range is rngX(A:D), I want to be able to allow the user to remove a column, let's say C and end up with the range rngX(A:C) which [I could then pass as rngX to the function] now contains the data from columns A, B, and D just as Excel does when you delete part of a column and it asks which way you want to shift the cells. I will give it a try later, I am not quite at that place in the project yet. Again thanks for your response. Pieter On Jul 26, 8:59*am, "Rick Rothstein" wrote: I've not used the LinEst function before, so I'm guessing here, but it sounds like what you want to use is the Resize property of the range. See if this does what you want... Result = Application.WorksheetFunction.LinEst(rngY.Resize(5 ), rngX.Resize(5), True, True) Now, that resize takes place from the first row. If instead of the first 5 rows, you wanted the middle 5 rows (from a group of 11 rows total for example), then you would use the Resize property in conjunction with the Offset property... Result = Application.WorksheetFunction.LinEst(rngY.Offset(4 ).Resize(5), rngX.Offset(4).Resize(5), True, True) Check out the Resize and Offset properties (of the Range object) in the help files to see about handling the columns via the optional 2nd argument for them. -- Rick (MVP - Excel) "Pieter" wrote in message ... On Jul 25, 6:21 pm, Pieter wrote: Typos (sorry): Change: Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved To: Which will correlate the Y variable with 2nd X variable. I would also like to be able to do the same thing for multiple columns which is also a problem I have not solved Change Ystore(j) - rngY(J) to: Ystore(j) = rngY(J) Pieter Hello: I have a routine that asks the user to select two ranges (using application.input box). One of the ranges is a column vector rngY and the second is a matrix or column vector rngX. The two regions are required to have the same number of rows and the matrix must be contiguous. These two are passed to linest() function in Excel as: Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True) Everything works fine and I get the expected results. Now I want to change the result so that only the first n rows are used. Something like: Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5), rngX.rows(1 to 5), True, True) where the actual number of rows in rngY and rngX might be 10. ie I want to use the first half the data to run the regression. I know that for columns I can use: Result = Application.WorksheetFunction.LinEst(rngY.Columns( 1), rngX.Columns(2), True, True) Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved I could something like (which would allow me to select any subset of rows): Startrow = 1 Endrow = 5 For i = 1 To col For j = Startrow To endrow Xstore(j, i) = rngX(j, i) Next j Next i For j = Startrow To endrow Ystore(j) - rngY(J) next j Then Xstore and Ystore would contain the right data but this creates two problems, it is slow and the linest function expect ranges. Can someone suggest a strategy? Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing select ranges in VBA
Re-read my posting.. I talk about using the Offset property to move the
starting point and then Resize after doing that (see the example I posted). -- Rick (MVP - Excel) "Pieter" wrote in message ... Hello Rick: Your suggestion was exactly what I was looking for. It works beautifully for the row problem. Thank you. I have not tried the column solution yet. I think the reisze option only works at the start or the end of a block of columns (or rows). I need to be able to delete columns from the middle and have the range "resized in the middle." For example if the range is rngX(A:D), I want to be able to allow the user to remove a column, let's say C and end up with the range rngX(A:C) which [I could then pass as rngX to the function] now contains the data from columns A, B, and D just as Excel does when you delete part of a column and it asks which way you want to shift the cells. I will give it a try later, I am not quite at that place in the project yet. Again thanks for your response. Pieter On Jul 26, 8:59 am, "Rick Rothstein" wrote: I've not used the LinEst function before, so I'm guessing here, but it sounds like what you want to use is the Resize property of the range. See if this does what you want... Result = Application.WorksheetFunction.LinEst(rngY.Resize(5 ), rngX.Resize(5), True, True) Now, that resize takes place from the first row. If instead of the first 5 rows, you wanted the middle 5 rows (from a group of 11 rows total for example), then you would use the Resize property in conjunction with the Offset property... Result = Application.WorksheetFunction.LinEst(rngY.Offset(4 ).Resize(5), rngX.Offset(4).Resize(5), True, True) Check out the Resize and Offset properties (of the Range object) in the help files to see about handling the columns via the optional 2nd argument for them. -- Rick (MVP - Excel) "Pieter" wrote in message ... On Jul 25, 6:21 pm, Pieter wrote: Typos (sorry): Change: Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved To: Which will correlate the Y variable with 2nd X variable. I would also like to be able to do the same thing for multiple columns which is also a problem I have not solved Change Ystore(j) - rngY(J) to: Ystore(j) = rngY(J) Pieter Hello: I have a routine that asks the user to select two ranges (using application.input box). One of the ranges is a column vector rngY and the second is a matrix or column vector rngX. The two regions are required to have the same number of rows and the matrix must be contiguous. These two are passed to linest() function in Excel as: Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True) Everything works fine and I get the expected results. Now I want to change the result so that only the first n rows are used. Something like: Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5), rngX.rows(1 to 5), True, True) where the actual number of rows in rngY and rngX might be 10. ie I want to use the first half the data to run the regression. I know that for columns I can use: Result = Application.WorksheetFunction.LinEst(rngY.Columns( 1), rngX.Columns(2), True, True) Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved I could something like (which would allow me to select any subset of rows): Startrow = 1 Endrow = 5 For i = 1 To col For j = Startrow To endrow Xstore(j, i) = rngX(j, i) Next j Next i For j = Startrow To endrow Ystore(j) - rngY(J) next j Then Xstore and Ystore would contain the right data but this creates two problems, it is slow and the linest function expect ranges. Can someone suggest a strategy? Thank you. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing select ranges in VBA
Hello:
I will work through it and see if I can understand what you are suggesting with respect to columns. I really have not yet tackled it so I have not worked through it or thought about the details yet. I might post back if I can't work it out. Again thanks. Pieter On Jul 26, 5:50*pm, "Rick Rothstein" wrote: Re-read my posting.. I talk about using the Offset property to move the starting point and then Resize after doing that (see the example I posted). -- Rick (MVP - Excel) "Pieter" wrote in message ... Hello Rick: Your suggestion was exactly what I was looking for. It works beautifully for the row problem. Thank you. I have not tried the column solution yet. I think the reisze option only works at the start or the end of a block of columns (or rows). I need to be able to delete columns from the middle and have the range "resized in the middle." For example if the range is rngX(A:D), I want to be able to allow the user to remove a column, let's say C and end up with the range rngX(A:C) which [I could then pass as rngX to the function] now contains the data from columns A, B, and D just as Excel does when you delete part of a column and it asks which way you want to shift the cells. I will give it a try later, I am not quite at that place in the project yet. Again thanks for your response. Pieter On Jul 26, 8:59 am, "Rick Rothstein" wrote: I've not used the LinEst function before, so I'm guessing here, but it sounds like what you want to use is the Resize property of the range. See if this does what you want... Result = Application.WorksheetFunction.LinEst(rngY.Resize(5 ), rngX.Resize(5), True, True) Now, that resize takes place from the first row. If instead of the first 5 rows, you wanted the middle 5 rows (from a group of 11 rows total for example), then you would use the Resize property in conjunction with the Offset property... Result = Application.WorksheetFunction.LinEst(rngY.Offset(4 ).Resize(5), rngX.Offset(4).Resize(5), True, True) Check out the Resize and Offset properties (of the Range object) in the help files to see about handling the columns via the optional 2nd argument for them. -- Rick (MVP - Excel) "Pieter" wrote in message ... On Jul 25, 6:21 pm, Pieter wrote: Typos (sorry): Change: Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved To: Which will correlate the Y variable with 2nd X variable. I would also like to be able to do the same thing for multiple columns which is also a problem I have not solved Change Ystore(j) - rngY(J) to: Ystore(j) = rngY(J) Pieter Hello: I have a routine that asks the user toselecttwo ranges (using application.input box). One of the ranges is a column vector rngY and the second is a matrix or column vector rngX. The two regions are required to have the same number of rows and the matrix must be contiguous. These two are passed to linest() function in Excel as: Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True) Everything works fine and I get the expected results. Now I want to change the result so that only the first n rows are used. Something like: Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5), rngX.rows(1 to 5), True, True) where the actual number of rows in rngY and rngX might be 10. ie I want to use the first half the data to run the regression. I know that for columns I can use: Result = Application.WorksheetFunction.LinEst(rngY.Columns( 1), rngX.Columns(2), True, True) Which will correlate the Y variable with 2nd X variable. I would also like to be able to the same thing for multiple columns which is also I problem I have not solved I could something like (which would allow me toselectany subset of rows): Startrow = 1 Endrow = 5 For i = 1 To col For j = Startrow To endrow Xstore(j, i) = rngX(j, i) Next j Next i For j = Startrow To endrow Ystore(j) - rngY(J) next j Then Xstore and Ystore would contain the right data but this creates two problems, it is slow and the linest function expect ranges. Can someone suggest a strategy? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing name ranges | Excel Discussion (Misc queries) | |||
Select Non Contigouos Ranges | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming | |||
Select instersection of two ranges | Excel Programming | |||
VBA-Select several ranges using variables | Excel Programming |