VBA: Set range to columns in different worksheets

Question

I am trying to run the LinEst function through VBA. The problem that I am having is that my X-variables are in the same column but on different worksheets.

My question: Is it possible to combine these columns from the different sheets to one range?

Below is my attempt to code but it gets stuck on the Union part. I provided my sample as well.

Thank you in advance!

Sub FM()

Dim sResult As Worksheet
Set sResult = Sheets("Result")

Dim sY As Worksheet
Set sY = Sheets("Y")

Dim sX1 As Worksheet
Set sX1 = Sheets("X1")

Dim sX2 As Worksheet
Set sX2 = Sheets("X2")

Dim sX3 As Worksheet
Set sX3 = Sheets("X3")


Dim sX4 As Worksheet
Set sX4 = Sheets("X4")

Dim x() As Variant
    ReDim x(1 To 4)
    x(1) = sX1.Columns("A")
    x(2) = sX2.Columns("A")
    x(3) = sX3.Columns("A")
    x(4) = sX4.Columns("A")


Dim rY As Range
Set rY = sY.Columns("A")


sResult.Range("B2").Value = Application.WorksheetFunction.LinEst(rY, x, True, True)(1, 4)


End Sub

Sample

@AxRo What you are trying to accomplish is impossible as far as I am aware. You can't create one range out of ranges that exist in multiple worksheets. A `Range` is a member of the `Worksheet` property and as such it must respect the container it resides in. What are you trying to accomplish? I can't imagine a need for something like this. — Brandon Barney, May 08 '17 at 12:27
Combine the values in to an array? I think the `LinEst` formula should work on an array of values. But note that your arrays should be of same size for `known_x's` and `known_y's`. — David Zemens, May 08 '17 at 12:28
@BrandonBarney the reason why these x's are in different sheets is because I have a very large dataset. Meaning that each x - variable has 800+ columns so it would be impractical to combine this in the same worksheet. — AxRo, May 08 '17 at 12:34
@David Zemens is correct. You'll need to add your data into arrays. Try the following: http://stackoverflow.com/questions/21267540/can-an-array-be-used-within-the-linest-function-in-vba — John Muggins, May 08 '17 at 12:39
if you get stuck, update your question to show the code you're using and where you're stuck :) — David Zemens, May 08 '17 at 12:45
After updating my code as edited in my question I get a type mismatch error. Any clue on where this code goes wrong? — AxRo, May 08 '17 at 13:01

score 0 · Answer 1 · answered May 08 '17 at 14:04

I don't want to put a useless answer, but if you play a bit with it, you will find something useful. And it produces some result in B2:

Option Explicit

Sub FM()

    Dim sResult As Worksheet
    Set sResult = Sheets(1)

    Dim sY As Worksheet
    Set sY = Sheets(2)

    Dim sX1 As Worksheet
    Set sX1 = Sheets(3)

    Dim sX2 As Worksheet
    Set sX2 = Sheets(4)

    Dim sX3 As Worksheet
    Set sX3 = Sheets(6)        

    Dim sX4 As Worksheet
    Set sX4 = Sheets(5)

    Dim x() As Variant
        ReDim x(1 To 4)
        x(1) = sX1.Cells(1, 1).Value
        x(2) = sX1.Cells(2, 1).Value
        x(3) = sX1.Cells(3, 1).Value
        x(4) = sX1.Cells(4, 1).Value


    Dim rY As Range
    Set rY = sY.Range(sY.Cells(1, 1), sY.Cells(5, 1))

    sResult.Range("B2").Value = Application.WorksheetFunction.LinEst(rY(1, 1), x(1), True, True)

End Sub

The problem is mainly in the way you refer to Arrays and Ranges. Check all of them again and you can make a workable solution.

score 0 · Accepted Answer · edited May 23 '17 at 12:18

In your update, x is an Array of Range objects but it needs to be an array of values from each respective range. That is almost certainly the mismatch error.

Resolving that, you'll need to fix your ranges, too, because it seems unlikely that you're using 4 million rows of data (Excel 2007+ has 1048576 rows per worksheet). We can use a method from this answer to help obtain the last row with data from a given column range.

This should get your x values and put them in an array known_x and the known_y array also, which you can use in your LineEst function.

Dim known_x()  'Will contain all of your x values
Dim known_y()
Dim i As Long
Dim rng As Variant
Dim val As Variant
Dim ws As Variant
Dim obs As Long
Dim SHEET_NAMES As String 'Comma-separated list of worksheets

SHEET_NAMES = "X1,X2,X3,X4"

'## Gets the size of the array needed to contain all of the values
For Each ws In Worksheets(Split(SHEET_NAMES, ","))
    With ws
        obs = obs + GetLastRow(.Columns(1))
    End With
Next
ReDim known_x(1 To obs)

'## Dump the values in to the array
i = 1
For Each ws In Worksheets(Split(SHEET_NAMES, ","))
    With ws
        Set rng = .Range("A1:A" & GetLastRow(.Columns(1)))
        For Each val In rng.Value
            known_x(i) = val
            i = i + 1
        Next
    End With
Next

'## Dump your y in to an array
With Worksheets("Sheet2")
    Set rng = .Range("A1:A" & GetLastRow(.Columns(1)))
    known_y = Application.Transpose(rng.Value))
End With

NOTE: If you are in fact using 4 million+ observations, then I think your known_y's parameter may be wrong, because that should be the same size as known_x's in the LinEst function, and you will need to add logic to ensure the arrays are the same size, etc.

NOTE: I've no idea what you're doing with (1, 4) at the end of your LinEst function call.

Thank you for your answer. The `(1, 4)` are coordinates in the `LinEst` function calling a certain bèta value. — AxRo, May 08 '17 at 15:52

VBA: Set range to columns in different worksheets

2 Answers2