CODEWITHSUNDEEP
arraysvbaexcel
Ans
Ans

Reputation: 1234

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

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.

Upvotes: 0

Views: 38

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 2

Related Questions