CODEWITHSUNDEEP
excelvba
Sancho Almeda
Sancho Almeda

Reputation: 151

2D Array inside an Array to Excel Output using Range

I have recently been looking into Arrays to write data faster into Excel for reports.

I have come across this, VBA pasting 3 dimensional array into sheet, and it seemed fit to what I want to do. However, I want to do it in only one (1) sheet.

Dim arr(1 To 3)

Dim a As Integer
Dim x As Integer
Dim y As Integer

For a = 1 To 3
    ReDim inner(1 To 5, 1 To 5)

    'don't worry...makes a copy
    arr(a) = inner

    For x = 1 To 5
        For y = 1 To 5
            arr(a)(x, y) = a * x * y
        Next
    Next

    Sheets(a).Select    
    Range(Cells(1, 1), Cells(5, 5)) = arr(a)
Next

Is it possible to output arr into excel range without looping? Something like:
Range(Cells(1, 1), Cells(5, 5*3)) = arr

Expected Output:

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

I tried doing it buy I got #N/A on my cells as outputs

Upvotes: 1

Views: 302

Answers (2)

brettdj
brettdj

Reputation: 55672

You can do this all with arrays as below

This line

Sheets(1).[a1].Offset(0, UBound(inner) * (lngCnt - 1)).Resize(UBound(inner, 1), UBound(inner, 2)) = arr(lngCnt) says

  • Sheets(1).[a1] .... Start at Sheet1 A1
  • Resize(UBound(inner, 1), UBound(inner, 2)) ... Offset A1 for each subsequent loop by the size of inner (ie 5, so the second loop works on F1, the third on K1)
  • Resize(UBound(inner, 1), UBound(inner, 2)) ... dump to a range equal to the size of inner (ie 5*5)

code

Dim arr(1 To 3)
Dim a As Long
Dim x As Long
Dim y As Long
Dim lngCnt As Long

For a = 1 To 3
    ReDim inner(1 To 5, 1 To 5)
    arr(a) = inner
    For x = 1 To 5
        For y = 1 To 5
            arr(a)(x, y) = a * x * y
        Next
    Next
Next

For lngCnt = 1 To UBound(arr)
    Sheets(1).[a1].Offset(0, UBound(inner) * (lngCnt - 1)).Resize(UBound(inner, 1), UBound(inner, 2)) = arr(lngCnt)
Next

enter image description here

Upvotes: 1

user2480047
user2480047

Reputation:

Answer after your edited question.

 Dim sheetNo As Integer
   sheetNo = 1
   Sheets(sheetNo).Select

   Dim startRow As Integer
   Dim endRow As Integer
   Dim startCol As Integer
   Dim endCol As Integer
   Dim totCols As Integer
   Dim lastCol As Integer
   Dim firstCol As Integer
   totCols = 5
   startRow = 2
   endRow = 5
   firstCol = 3
   For curRow = startRow To endRow
      lastCol = firstCol
      For a = 1 To 3
         startCol = lastCol + 1
         endCol = startCol + totCols
         For curCol = startCol To endCol
           ActiveSheet.Cells(curRow, curCol).Value = a
         Next
         endCol = endCol + 1
         If (a < 3) Then
            ActiveSheet.Cells(curRow, endCol).Value = "-"
         End If
         lastCol = endCol
      Next
   Next

Upvotes: 0

Related Questions