CODEWITHSUNDEEP
excelvba
Jens
Jens

Reputation: 185

Can't write array to sheet

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

ReDim HighScoreOneHourData(1 To UBound(SA, 1) - 3)
ReDim HighScoreOneHourDates(1 To UBound(SA, 1) - 3)

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?

Upvotes: 0

Views: 67

Answers (2)

chris neilsen
chris neilsen

Reputation: 53136

It's been explained 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

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166126

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.

Upvotes: 1

Related Questions