Code reuse. Your procedure combines two arrays. You want to combine arrays
A, B, C, D, E. Use your procedure to bind A and B, then reuse it to combine
[AB] and C, [ABC] and D, and finally [ABCD] and E.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Albert" wrote in message
...
Hello and Happy New Year...
I recently created a nice little procedure that "bonds" two arrays
toghether
(see below). I now want to make a procedure that bonds a variable number
of
arrays (they are all two dimentional and all have the same horizontal
Ubound). For example, sometimes I have to bond only 2 arrays, but
sometimes I
may have to bond 5 of them toghether. I haven't been able to figure it
out.
Perhaps someone has done something similar or has some ideas?
Thanks in advance,
Albert C

Public RegistrosExistentes() As Variant
Public Const TotalFields = 13
Sub ConstruirArrayRegistrosExistentes(Arr1, Arr2)
ReDim RegistrosExistentes(1 To (UBound(Arr1, 1) + UBound(Arr2, 1)), 1 To
TotalFields)

For x = 1 To UBound(Arr1, 1)
For Y = 1 To TotalFields
RegistrosExistentes(x, Y) = Arr1(x, Y)
Next Y
Next x
For x = 1 To UBound(Arr2, 1)
For Y = 1 To TotalFields
RegistrosExistentes(UBound(Arr1, 1) + x, Y) = Arr2(x, Y)
Next Y
Next x
End Sub

Hi Jon,

Good idea but since Albert uses a global variable he would have to
copy/save the result array in each pass.

I suggest:

Option Explicit

Public RegistrosExistentes() As Variant
Public Const TotalFields = 2
Sub ConstruirArrayRegistrosExistentes(ParamArray vArr())
Dim i As Long, j As Long, k As Long
Dim vE As Variant

i = 0
For Each vE In vArr
i = i + UBound(vE, 1)
Next vE
ReDim RegistrosExistentes(1 To i, 1 To TotalFields)

i = 0
For Each vE In vArr
For j = 1 To UBound(vE, 1)
i = i + 1
For k = 1 To TotalFields
RegistrosExistentes(i, k) = vE(j, k)
Next k
Next j
Next vE
End Sub

Sub Test()
Dim vA(1 To 3, 2) As Variant
Dim vB(1 To 4, 2) As Variant
Dim vC(1 To 5, 2) As Variant
Dim i As Long, j As Long

vA(1, 1) = 11
vA(1, 2) = 12
vA(2, 1) = 21
vA(2, 2) = 22
vA(3, 1) = 31
vA(3, 2) = 32

vB(1, 1) = 41
vB(1, 2) = 42
vB(2, 1) = 51
vB(2, 2) = 52
vB(3, 1) = 61
vB(3, 2) = 62
vB(4, 1) = 71
vB(4, 2) = 72

vC(1, 1) = 81
vC(1, 2) = 82
vC(2, 1) = 91
vC(2, 2) = 92
vC(3, 1) = 101
vC(3, 2) = 102
vC(4, 1) = 111
vC(4, 2) = 112
vC(5, 1) = 121
vC(5, 2) = 122

Call ConstruirArrayRegistrosExistentes(vA, vB, vC)

For i = 1 To UBound(RegistrosExistentes, 1)
For j = 1 To UBound(RegistrosExistentes, 2)
Debug.Print RegistrosExistentes(i, j);
Next j
Debug.Print
Next i

End Sub

Albert, set Totalfields to 13 again after testing.

Regards,
Bernd

Good point. I looked quickly at his code and thought his sub was really a
function that returned the merged array. I would have suggested not using a
global variant array.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
Hi Jon,

Good idea but since Albert uses a global variable he would have to
copy/save the result array in each pass.

I suggest:

Option Explicit

Public RegistrosExistentes() As Variant
Public Const TotalFields = 2
Sub ConstruirArrayRegistrosExistentes(ParamArray vArr())
Dim i As Long, j As Long, k As Long
Dim vE As Variant

i = 0
For Each vE In vArr
i = i + UBound(vE, 1)
Next vE
ReDim RegistrosExistentes(1 To i, 1 To TotalFields)

i = 0
For Each vE In vArr
For j = 1 To UBound(vE, 1)
i = i + 1
For k = 1 To TotalFields
RegistrosExistentes(i, k) = vE(j, k)
Next k
Next j
Next vE
End Sub

Sub Test()
Dim vA(1 To 3, 2) As Variant
Dim vB(1 To 4, 2) As Variant
Dim vC(1 To 5, 2) As Variant
Dim i As Long, j As Long

vA(1, 1) = 11
vA(1, 2) = 12
vA(2, 1) = 21
vA(2, 2) = 22
vA(3, 1) = 31
vA(3, 2) = 32

vB(1, 1) = 41
vB(1, 2) = 42
vB(2, 1) = 51
vB(2, 2) = 52
vB(3, 1) = 61
vB(3, 2) = 62
vB(4, 1) = 71
vB(4, 2) = 72

vC(1, 1) = 81
vC(1, 2) = 82
vC(2, 1) = 91
vC(2, 2) = 92
vC(3, 1) = 101
vC(3, 2) = 102
vC(4, 1) = 111
vC(4, 2) = 112
vC(5, 1) = 121
vC(5, 2) = 122

Call ConstruirArrayRegistrosExistentes(vA, vB, vC)

For i = 1 To UBound(RegistrosExistentes, 1)
For j = 1 To UBound(RegistrosExistentes, 2)
Debug.Print RegistrosExistentes(i, j);
Next j
Debug.Print
Next i

End Sub

Albert, set Totalfields to 13 again after testing.

Regards,
Bernd

If you have to use the array in a sequence of procedures, one procedure
could simply pass it to the next.

If you have to keep it around for a later procedure, you could store it in a
temporary worksheet (as long as it isn't too large) or in a disconnected
recordset, and load it again when needed.

If you need to, you could stick with the global, but I don't like to use
them. I'm always mucking with my code when it's running, and that sometimes
halts execution and usually wipes out my variables.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Albert" wrote in message
...
Hi Jon, Thanx for your suggestion.
I'm using the Global Array because I have tu use the same array later and
for many different procedures. Also, the merged array becomes extremely
large
so I don't want to have to reload it. Maybe you could suggest something I
could do that does not involve globar Array?
Albert C

"Jon Peltier" wrote:

Good point. I looked quickly at his code and thought his sub was really a
function that returned the merged array. I would have suggested not using
a
global variant array.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
Hi Jon,

Good idea but since Albert uses a global variable he would have to
copy/save the result array in each pass.

I suggest:

Option Explicit

Public RegistrosExistentes() As Variant
Public Const TotalFields = 2
Sub ConstruirArrayRegistrosExistentes(ParamArray vArr())
Dim i As Long, j As Long, k As Long
Dim vE As Variant

i = 0
For Each vE In vArr
i = i + UBound(vE, 1)
Next vE
ReDim RegistrosExistentes(1 To i, 1 To TotalFields)

i = 0
For Each vE In vArr
For j = 1 To UBound(vE, 1)
i = i + 1
For k = 1 To TotalFields
RegistrosExistentes(i, k) = vE(j, k)
Next k
Next j
Next vE
End Sub

Sub Test()
Dim vA(1 To 3, 2) As Variant
Dim vB(1 To 4, 2) As Variant
Dim vC(1 To 5, 2) As Variant
Dim i As Long, j As Long

vA(1, 1) = 11
vA(1, 2) = 12
vA(2, 1) = 21
vA(2, 2) = 22
vA(3, 1) = 31
vA(3, 2) = 32

vB(1, 1) = 41
vB(1, 2) = 42
vB(2, 1) = 51
vB(2, 2) = 52
vB(3, 1) = 61
vB(3, 2) = 62
vB(4, 1) = 71
vB(4, 2) = 72

vC(1, 1) = 81
vC(1, 2) = 82
vC(2, 1) = 91
vC(2, 2) = 92
vC(3, 1) = 101
vC(3, 2) = 102
vC(4, 1) = 111
vC(4, 2) = 112
vC(5, 1) = 121
vC(5, 2) = 122

Call ConstruirArrayRegistrosExistentes(vA, vB, vC)

For i = 1 To UBound(RegistrosExistentes, 1)
For j = 1 To UBound(RegistrosExistentes, 2)
Debug.Print RegistrosExistentes(i, j);
Next j
Debug.Print
Next i

End Sub

Albert, set Totalfields to 13 again after testing.

Regards,
Bernd

Hello Albert,

Maybe this site can help you:
http://www.dailydoseofexcel.com/arch...ced-functions/

Regards,
Bernd