Can't write array to sheet

Question

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?

You need to size your arrays using redim because you have declared your arrays as dynamic ( '()'). Given that this is another post from you on a similar topic, you might be best served by doing some reading up on VBA arrays. SO is supposed to be a 'last resort' resource not an 'opps I've tripped over a matchstick' resource. — freeflow
– freeflow, Commented Oct 10, 2022 at 16:10
Oh sorry, I posted the wrong code. The first two lines should be ReDim HighScoreOneHourData(1 To UBound(SA, 1) - 3) ReDim HighScoreOneHourDates(1 To UBound(SA, 1) - 3) — Jens
– Jens, Commented Oct 11, 2022 at 7:22
Btw, I always google extensively before I post on here. Couldn't find an answer to my question so this was my last resort. :) — Jens
– Jens, Commented Oct 11, 2022 at 7:45

chris neilsen · Accepted Answer · 2022-10-10 22:03:17Z

2

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

edited Oct 10, 2022 at 22:03

answered Oct 10, 2022 at 17:46

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

1 Comment

Jens Over a year ago

Thank you! Your solution worked too! Maybe it's better than transpose? Fewer steps?

Tim Williams · Accepted Answer · 2022-10-10 16:46:10Z

1

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.

answered Oct 10, 2022 at 16:46

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

1 Comment

Jens Over a year ago

Thank you! Transpose did the trick. I need about 5000 elements so well within the limits.

Collectives™ on Stack Overflow

Can't write array to sheet

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related