1

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

Improve this question
5
  • it isn't clear to me what you want to do. The current code does write the ranges without loops, the loop is being used to write to different sheets Commented Jun 20, 2013 at 8:59
  • @ brettdj I am sorry. I think it may not be clear what I wanted so I edited the 1st part of the question. Commented Jun 20, 2013 at 9:03
  • That is a bit clearer, but why do you do you expect the first 3 values of arr(1)(1) to be output in the manner above? Commented Jun 20, 2013 at 9:18
  • @brettdj Honestly, this is my first time doing these type of array which is rendering me a bit at a loss on how to phrase my question so I apologise. Basically, let's say I have 3 5x5 arrays. Array1 has the values of 1's and Array2 has the values of 2's and so on. I want to write them in an excel file horizontally without looping. Is that possible? Commented Jun 20, 2013 at 9:28
  • Range("A1:E5") will be Array1; Range("F1:J5") will be Array2 and so on. Commented Jun 20, 2013 at 9:38

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for the answer. I have ended up with the same conclusion but your loop at the end is much better than what I have came up with as it takes into account quite a few things. Thanks again.
0

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
Improve this answer

9 Comments

Thanks for the reply varocarbas. As brettdj pointed out, I was a bit unclear on what I want to do. I would like to simply output arr in a range in one sheet without looping.
Sorry, I have replied without understanding properly what you want. I will come back to you in brief with a solution working properly.
There you have your code. I have preferred to change the approach: you don't need to rely on arrays for a so simple case, you just need to make sure that the loops perform the kind of actions you want. Bear in mind the differences between values of an array and dimensions of an array because from this code I am not sure if you have these ideas completely clear.
@varacarbas thank you for the alternative and effort you have put to this. This is a good piece of code for me to study.
But that is the problem. I need that Cell to Cell comparison since the only data I have would be from the subordinates. This data are then added to get the data for the Supervisors. And then the data for supervisors gets added to get data for program directors. Then take into account that there are 6 supervisors with 12 personnel each. I could have always changed it so that each data are processed before it is written in excel thus reducing cell to cell comparison but this is an old software and building a new software would be much better cost-wise and efficiency-wise.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.