Writing into cells from array to range: array is not fully displayed in range

Question

So I have an array with a lot of data, I used to write the data to excel sheet via for cycle, but it took too long, so I looked into faster alternatives.

Now I try to display the information with setting a value of range of cells directly to array:

Sub displayRandomMatrix(clientsColl As Collection, resultWorkbook As Workbook)

Dim NamesRange As Range

With resultWorkbook.Worksheets("matrix_random")
...
Set NamesRange = _
    .Range(.Cells(2, 1), .Cells(clientsColl.Count + 1, 1))
Dim NamesArray() As String
ReDim NamesArray(1 To clientsColl.Count)

Dim clientRow As Long
Dim simulation As Long
clientRow = 1
simulation = 1

Dim clientCopy As client
For Each clientCopy In clientsColl
    For simulation = 1 To clientCopy.getRandomNumbers.Count
           ...
    Next

    NamesArray(clientRow) = clientCopy.getClientName
    clientRow = clientRow + 1
Next
...
NamesRange.value = NamesArray
...
End With

'debugging
Debug.Print "**************start"
For clientRow = 1 To clientsColl.Count
   Debug.Print NamesArray(clientRow)
Next
Debug.Print "**************end"

End Sub

However when I then open a resultWorkbook I see that the same client's name is written in all the needed cells of the 1st column. At the same the debug section of the code produces correct output - there are correct multiple clients names in that array.

So something gets broken when I assign that array to a range: NamesRange.value = NamesArray.

At the same time I do similar thing with other arrays and it works, but this while comes out with the bug.

What might be the reason?

NOTE: clientsColl is a good, correct collection of Clients. There is nothing wrong with it, neither is with resultWorkbook.

Scott Craner · Accepted Answer · 2017-11-21 14:17:14Z

2

NamesArray is a horizontal array, which you are trying to assign to a vertical range. Try using Application.Transpose

NamesRange.value = Application.Transpose(NamesArray)

Transpose is a quick fix but has its limitations. So if that does not work you will need to force a vertical array by declaring a 2nd dimension in your array:

ReDim NamesArray(1 To clientsColl.Count, 1 to 1)

Then when you fill it make sure to include the second dimension:

NamesArray(clientRow,1) = clientCopy.getClientName

Then you can assign it as you have:

NamesRange.value = NamesArray

edited Nov 21, 2017 at 14:17

answered Nov 21, 2017 at 14:10

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

1 Comment

Ans Over a year ago

Thank you! I also have a one-dimensional array there in code, but I display it one row, in several columns (meaning, horizontally). Does the horizontal display mean I don't need to do the transformation with that array?

Collectives™ on Stack Overflow

Writing into cells from array to range: array is not fully displayed in range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related