Alternative to Union Function/Combining Ranges

[Hamond]

Hi,

I am trying to use the union method to combine different columns of data into one range. When the data is on the same sheet it works fine but when one of the ranges is on another sheet such as in the case below it generates a "method of union of object global failed" error.

[VBA]
Sub AAAtesting()
Set a = Sheets("sheet2").Cells(2, 3).Resize(120)
Set b = Sheets("sheet2").Cells(2, 4).Resize(120)
Set c = Sheets("Other").Cells(2, 11).Resize(120)

col = Union(a, b, c)
col.select 'or enter col as an argument into a formula

End Sub
[/VBA]


Internet searches reveal this is because the union function does not work accross different sheets. Is there an alternative way I can acheive what I want either using a modified union or another way to combine ranges?

Thanks,

Hamond

[Aflatoon]

What do you plan to do with it? There aren't that many things in Excel that work with 3D ranges.

[Hamond]

Hi,

The final objective is to put the entire range into a function that performs a principle component analysis/PCA which takes serveral variables/columns as input.Unfortunately, there will always be one column variable that sits in another sheet which will update iteratively.

[Aflatoon]

Can you not simply pass that variable separately?

[Hamond]

No, all variables have to be passed together to the function which only takes one argument – a range of data. The variables must be in a continuos range, i.e. grouped together in adjacent columns like column B, C, D, E F. In situations where they are not grouped together, (e.g. columns B, E,) I use the union method to combine from different columns into one single range.

[Aflatoon]

Then I think you need to rethink your workbook layout, or link another cell to the iterative cell.

[mikerickson]

All the cells in a range object have to be on the same worksheet.

If you want to combine ranges from different sheets into one "bag", you could use a Collection. It doesn't have any of the properties of a Range object, but since working with discontinous ranges almost always involes looping through Areas, looping through the Collection isn't that much more of a bother.

[rcharters]

It's interesting that Union does not work across worksheets. Give this code a try, should do what you are looking for.


[vba] Sheet2.Columns("M:O").Clear
aColumns = Array(Sheet2.Range("C2:C121"), Sheet2.Range("D2121"), Sheet1.Range("K2:K121"))
i = 12 'Column "L"
For Each vOne In aColumns
i = i + 1
vData = vOne
Sheet2.Range(Cells(2, i), Cells(121, i)).Value = vData
Next vOne
[/vba]

[Nick_London]

Function MakeContig(ParamArray av() As Variant) As Variant
Dim avOut() As Variant
Dim i As Long
Dim j As Long
ReDim avOut(1 To av(0).Count, 0 To UBound(av))
For j = 0 To UBound(av)
For i = 1 To av(j).Rows.Count
avOut(i, j) = av(j)(i)
Next i
Next j
MakeContig = avOut
End Function
[/vba]

Nick

[Nick_London]

Try using this function that takes mutiple arrays/ranges and treats as one. Have no idea how it works.

Function MakeContig(ParamArray av() As Variant) As Variant
Dim avOut() As Variant
Dim i As Long
Dim j As Long
ReDim avOut(1 To av(0).Count, 0 To UBound(av))
For j = 0 To UBound(av)
For i = 1 To av(j).Rows.Count
avOut(i, j) = av(j)(i)
Next i
Next j
MakeContig = avOut
End Function

[Hamond]

Thanks for the suggestions.

Not really sure how to use collections in the context of what I am trying to do.

I tried running the code but am getting an run-time error 424 - Object required. I've tried both versions of the line below in the code. Note that I changed "sheet1" to "other", the actual name of the second sheet.

[vba]acolumns = Array(Sheet2.Range("C2:C121"), Sheet2.Range("D2121"), other.Range("K2:K121"))
[/vba]

[vba]
acolumns = Array(Worksheets("Sheet2").Range("C2:C121"), Worksheets("Sheet2").Range("D2121"), Worksheets("other").Range("K2:K121"))

[/vba]

Hamond

[mikerickson]

You mentioned a UDF that takes a range of cells and returns " principle component analysis/PCA".

You say that it only takes one argument, a Range argument. If this UDF isn't under your control and it does accept only one argument, then you are stuck, all the cells of that range must be on the same sheet.

If you can alter the UDF, then you should alter it to accept 3 range arguments (the a,b,c from the OP).

[Hamond]

Hi

Yes the UDF takes a range of data as inputs (so each column in a selected area (e.g a1 to E500) is treated as seperate variable by the function. I may be able to request access to the UDF but even if I did I wouldn't know what changes I need to make!

So there is no way you can write the data from different sheets to a array/table, store it in memory and then select that array as input? I'm not an expert and probabaly misunderstanding what's possible but I would have assumed if you store the data into an multivariable array, then as far as the function is concerned, the data would all be coming from the same input source?

[mdmackillop]

Something like
[VBA]Option Base 1

Sub test()
Cells(1, 1).Resize(20, 3) = CollectData
End Sub

Function CollectData() As Variant
Dim arr()
ReDim arr(20, 3)
For i = 1 To 3
For j = 1 To 20
arr(j, i) = Sheets(i).Cells(j, 3)
Next
Next
CollectData = arr
End Function
[/VBA]

[Aflatoon]

It depends on the UDF. If its input is typed As Range, then passing an array will not work; if typed As Variant, then it will. Either way it sound to me as though it could have been better written by taking separate inputs. Easier to code too.

[Hamond]

Hi, below is the first line of the function. Based on comments from Aflatoon, I am assuming this is declared as a variant?

[VBA]Function PCA(VarRange As Range, Optional IncludeAddStats As Boolean = True) As Variant[/VBA]

Mdmackilliop, thanks for the example but do not fully understand how to utalise. For example, where would I specify the actual ranges to read in?, i.e the line:

acolumns = Array(Sheet2.Range("C2:C121"), Sheet2.Range("D2121"), other.Range("K2:K121")).

Presumably if I have 120 rows in the data then I would need to set ReDim arr(120, 3), where 3 relates to 3 columns of data (not sheets)?

Thanks,

Hamond

[mikerickson]

What is the code for all of your UDF?

[Paul_Hossler]

If you have the UDF code, you could change the call to allow multiple sheets, including a Union with in a sheet

[VBA]
Option Explicit

Sub test()
Call PCA(True, _
Union(Worksheets("sheet1").Range("A:G"), Worksheets("sheet1").Range("R:Z")), _
Worksheets("sheet2").Range("D:H"), _
Worksheets("sheet3").Range("C:V"), _
Worksheets("sheet4").Range("A:Z"))

End Sub

Function PCA(IncludeAddStats As Boolean, ParamArray VarRange()) As Variant
Dim i As Long

For i = LBound(VarRange) To UBound(VarRange)
MsgBox VarRange(i).Address(True, True, xlA1, True)
Next i
End Function
[/VBA]

Paul

[Aflatoon]

Hi, below is the first line of the function. Based on comments from Aflatoon, I am assuming this is declared as a variant?

No, the function returns a Variant, but requires a Range to be passed, so an array would not work.