Can't write array to sheet-CodePudding

Dim HighScoreOneHourData() As Integer
Dim HighScoreOneHourDates() As String

For j = 4 To UBound(SA, 1)
    HighScoreOneHourData(j - 3) = CInt(Val(SA(j, PositionInArray   DataColumn   2)))
    HighScoreOneHourDates(j - 3) = SA(j, 1)
Next j

SortSheet.Range("A1:A" & UBound(HighScoreOneHourDates)) = HighScoreOneHourDates
SortSheet.Range("B1:B" & UBound(HighScoreOneHourData)) = HighScoreOneHourData

When these last two lines in the example above are executed all the cells in the sheets are filled with the first element from the arrays.

HighScoreOneHourDates is an array filled with consecutive dates. Still only the first date is printed to the sheet.

I've stopped the code and checked the state of the arrays and the they are correctly filled.

Anyone knows why the cells are filled with the first element?

CodePudding user response：

A 1D array always wants to be placed on a sheet in a row, not a column. That's why you only get the first element repeated. You need to re-orient the array to put it in a column, or make your arrays 2D (1 To numHere, 1 To 1)

Note there is a limit to the array size you can pass to Transpose of around 63-64k elements.

Assuming your arrays are 1-based you can do this:

SortSheet.Range("A1:A" & UBound(HighScoreOneHourDates)) = _
                     Application.Transpose(HighScoreOneHourDates)

for example.

CodePudding user response：

It's been examined why 1D arrays don't work for you. A better fix is to Dim them as 2D

ReDim HighScoreOneHourData(1 To UBound(SA, 1), 1 To 1) As Integer
ReDim HighScoreOneHourDates(1 To UBound(SA, 1), 1 To 1) As String

For j = 4 To UBound(SA, 1)
    HighScoreOneHourData(j - 3, 1) = CInt(Val(SA(j, PositionInArray   DataColumn   2)))
    HighScoreOneHourDates(j - 3, i) = SA(j, 1)
Next j

SortSheet.Range("A1:A" & UBound(HighScoreOneHourDates, 1)) = HighScoreOneHourDates
SortSheet.Range("B1:B" & UBound(HighScoreOneHourData, 1)) = HighScoreOneHourData