Changing select ranges in VBA

[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.

 

[Pieter]

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

 

[Rick Rothstein]

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)


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

 

[Pieter]

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

 

[Rick Rothstein]

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)


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

 

[Pieter]

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

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)


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

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.

"Pieter" <[email protected]> wrote in message

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)