• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Outputting an array just repeats the first value over and over

P

PipBoy808

Member
This is an odd one.

In the below code, when I try to output an array (DateArray) into a vertical range (OutputRange), the first value of the array is just repeated over and over, like so:

01/01/2013
01/01/2013
01/01/2013
01/01/2013
01/01/2013
01/01/2013

However, this is not the case if I output the array to a horizontal range. Can anyone help me understand why this is the case?

Code: 
Set Dates = Range(Cells(11, 3), Cells(LastDate, 3))
 
For i = 1 To Dates.Rows.Count
  DelData.Add Dates(i, 1).Value, 1
 
Next i
DateArray = DelData.Keys
shtCodes.Activate
Set OutputRange = Range("Y10", Cells(10 + (Dates.Rows.Count), 25)) 'vertical output range
'Set OutputRange = Range("Y10", Cells(10, 25 + (Dates.Rows.Count))) 'horizontal outputrange works fine!
OutputRange = DateArray
 
PipBoy808

I'm going to suggest that it is the way you have defined DelData
But you haven't included that in the code snippet

So If the Horizontal array works then DelData is a Horizontal array
You will have to translate it to drop it into a vertical array or do it cell by cell (slower)
 
I'm not sure what the construction of DelData or DateArray looks like exactly, but my guess would be that DateArray is a horizontal array. You could add a Watch to the variable, to see how it's being built. If it's only a single row, that would certainly produce the results you are seeing.
 
DateArray is derived from the keys of a dictionary, DelData. So technically, the array doesn't really have a form, right?

Anyway, I tinkered with it some more and the following worked a treat:

Code: 
OutputRange = Application.Transpose(DateArray)

For some reason DateArray was horizontal by default, even though you can see in my first post that the initial range (Dates) is in a single column. It's as though it lost it's form when it entered the dictionary as a set of keys.
 
Hi PipBoy808,

it's not a Dictionary problem and not odd at all, totally normal and logical
when you know the data array structure in Excel !

A column array in Excel must have two dimensions : first for the rows and second for the columns
even for only one column, like the Cells property !

By using a break point in your code or F8 key and checking the Local Variables window,
you would have already discover that by yourself ! All tools at hand …

For example in this code an array in created upon values of column A1:A10 :
AR = [A1:A10].Value …

So what looks like the variable AR in the Local Variables window ?
Like it was declared as Dim AR(1 to 10, 1 to 1) : 10 rows x 1 column …

So when an array has only one dimension, like one from a Dictionary,
Excel understands it as a line, not as a column …

So you have to transpose the row in a column.
For example the Dictionary Keys must fill a column since cell M10 :​

Code: 
    [M10].Resize(Dict.Count).Value = Application.Transpose(Dict.Keys)
Regards !​
 
Marc L said:
Hi PipBoy808,

it's not a Dictionary problem and not odd at all, totally normal and logical
when you know the data array structure in Excel !

A column array in Excel must have two dimensions : first for the rows and second for the columns
even for only one column, like the Cells property !

By using a break point in your code or F8 key and checking the Local Variables window,
you would have already discover that by yourself ! All tools at hand …

For example in this code an array in created upon values of column A1:A10 :
AR = [A1:A10].Value …

So what looks like the variable AR in the Local Variables window ?
Like it was declared as Dim AR(1 to 10, 1 to 1) : 10 rows x 1 column …

So when an array has only one dimension, like one from a Dictionary,
Excel understands it as a line, not as a column …

So you have to transpose the row in a column.
For example the Dictionary Keys must fill a column since cell M10 :​

Code: 
    [M10].Resize(Dict.Count).Value = Application.Transpose(Dict.Keys)
Regards !​
Click to expand...

Great answer. Thanks very much.

PipBoy808,
Just curious, why are you using Sheet.Activate method?
Click to expand...

This was taken from a wider body of code where I'm working among different sheets.
 
You can mostly avoid those .Activate by qualifying the range.
Code: 
shtCodes.Activate
Set OutputRange = Range("Y10", Cells(10 + (Dates.Rows.Count), 25)) 'vertical output range
'Set OutputRange = Range("Y10", Cells(10, 25 + (Dates.Rows.Count))) 'horizontal outputrange works fine!
OutputRange = DateArray

to
Code: 
Set OutputRange = shtCodes.Range("Y10", Cells(10 + (Dates.Rows.Count), 25)) 'vertical output range
'Set OutputRange = Range("Y10", Cells(10, 25 + (Dates.Rows.Count))) 'horizontal outputrange works fine!
OutputRange.Value = DateArray
 
You must log in or register to reply here.
Back
Top