2D Array in a 2D array

[bingobongo]

Hi,

I'm wondering if it's possible to have a 2x2 array of random values inserted into the centre of a 6x6 array of zeros.

0,0,0,0,0,0
0,0,0,0,0,0
0,0,x,x,0,0
0,0,x,x,0,0
0,0,0,0,0,0
0,0,0,0,0,0

I also need to be able to extract the 2x2 array from the 6x6 array for use later in the code.

This process of inserting and extracting happens several times within loops of my code so I'm trying to not have to loop through each position of the array if possible, since the 2x2 and 6x6 are just example sizes (The actual sizes needed are 8x8 and 22x22). I've tried looping through the large array one place at a time and inserting the small array as it went through but I found it was taking too long. I was hoping I could just read/write it in one line with some modified version of SmallArray = range("A1:B2") since that is much quicker.

Let me know if this is possible and/or if you need more information. Thanks

 

[Marcelo Branco]

Say the 6x6 Array is in A1:F6, so to get the values in rows 3 and 4 and columns 3 and 4, maybe this

Sub aTest()
    Dim SmallArray As Variant
    
    SmallArray = Application.Index(Range("A1:F6"), Application.Transpose(Array(3, 4)), Array(3, 4))
    MsgBox SmallArray(1, 1)
End Sub

M.

 

[MickG]

If your code will allow ???, Try this, starting from "A1" for any matrix size of "0's" with smaller matrix of "X's", in "A1 currentregion.
First code removes the "X" matrix and places it starting "AA1".
Second code replaces the "X" matrix in first matrix

[COLOR="Navy"]Sub[/COLOR] MG07Jan32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1").CurrentRegion
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn = "X" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    nRng.Copy Range("AA1")
    nRng.ClearContents
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


[COLOR="Navy"]Sub[/COLOR] MG07Jan33
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1").CurrentRegion
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn = "" [COLOR="Navy"]Then[/COLOR] Dn = "X"
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[bingobongo]

Thank you both for your answers. I've tried testing them out and I found this line:

SmallArray = Application.Index(Range("A1:F6"), Application.Transpose(Array(3, 4)), Array(3, 4))
to be perfect for extracting the SmallArray from the large one. I needed to modify it a bit since my large array is not a reference to any cells in particular, it's just an empty array defined at the beginning of the code, but it works well.

With that said, is there anyway to reword it to perform the insertion of the small array into the large array?

I'm trying to keep the back and forth between the code and sheet to a minimum so I'm trying to do all this within the code itself. So the idea is that I've defined the LargeArray(1 to 6, 1 to 6) full of zeros at the beginning of the code. Then at some point I need to take range("A1:B2") and insert it into the centre of the LargeArray. After that, I run some code which modifies the combined LargeArray. And then I can now use your code to extract the SmallArray at the end.

So you've solved half of my problem so hopefully theres a simple way of inserting the range into the LargeArray as well.

Thanks again.

 

[Norie]

In Excel VBA there is no simple way to insert into an array.

The only way it's possible would be to loop, though you wouldn't only need to loop through the small array.

Sub InsertSmallArray()
Dim LargeArray(1 To 6, 1 To 6) As Long
Dim SmallArray(1 To 2, 1 To 2) As Long
Dim I As Long
Dim J As Long

    For I = LBound(SmallArray) To UBound(SmallArray)
        For J = LBound(SmallArray, 2) To UBound(SmallArray, 2)
            SmallArray(I, J) = Int(Rnd * 100) + 1
        Next J
    Next I
    
    For I = LBound(SmallArray) To UBound(SmallArray)
        For J = LBound(SmallArray, 2) To UBound(SmallArray, 2)
            LargeArray(2 + I, 2 + J) = SmallArray(I, J)
        Next J
    Next I
    
    Range("A1").Resize(UBound(LargeArray), UBound(LargeArray, 2)).Value = LargeArray
    
End Sub

 

[bingobongo]

Thanks for answering. I think I found a way to re-organize my code so that the inserting only happens once at the very start of the procedure instead of within the loops. Since it's only being done once now, a loop is fine and is what I've used now.

Thanks,

Bingo