Array data to Range: Only first value written

[Post Tenebras Lux]

I have created an array that stores prices. When my code writes the values
of the array (single dimension, about 1500 elements), it writes the value of
the first element to every cell of the worksheet range. Relevant code is as
follows:

Dim Px As Single, EMA_0 As Single, EMA_1 As Single
Dim arEMA() As Single

nData = UBound(arPxData())
ReDim arEMA(nData)

arEMA(1) = arPxData(1)
For i = 2 To nData
arEMA(i) = (2 / (1 + nPeriods) * (arPxData(i) - arPxData(i - 1))) +
arPxData(i - 1)
Next

With Range("OutEMA0Head")
Range(.Offset(1, 0), .Offset(nData, 0)).Clear
Range(.Offset(1, 0), .Offset(nData, 0)) = arEMA
End With

The Watch window shows that each element of the arEMA is filled with
different values. The .Clear line works perfectly. So does = arEMA line,
except all 1500 cells are filled with the same value - the first element of
arEMA.

I don't want to have to loop through every cell, but it makes no sense to me
why this is happening.

Any suggestions would be appreciated.

[Peter T]

You have defined a 1D horizontal array. If you were to do

[a1:c1].value = arEMA

the ROW should be populated with the first three values of your array

Try it like this

Redim arEMA(1 to rows),1 to cols)

looks like you only have one column but include the second dimension when
populating, eg
arEMA(1,1) = first value

Regards,
Peter T

"Post Tenebras Lux" <[email protected]> wrote in
message news:[email protected]...
> I have created an array that stores prices. When my code writes the
values
> of the array (single dimension, about 1500 elements), it writes the value
of
> the first element to every cell of the worksheet range. Relevant code is
as
> follows:
>
> Dim Px As Single, EMA_0 As Single, EMA_1 As Single
> Dim arEMA() As Single
>
> nData = UBound(arPxData())
> ReDim arEMA(nData)
>
> arEMA(1) = arPxData(1)
> For i = 2 To nData
> arEMA(i) = (2 / (1 + nPeriods) * (arPxData(i) - arPxData(i - 1)))
+
> arPxData(i - 1)
> Next
>
> With Range("OutEMA0Head")
> Range(.Offset(1, 0), .Offset(nData, 0)).Clear
> Range(.Offset(1, 0), .Offset(nData, 0)) = arEMA
> End With
>
> The Watch window shows that each element of the arEMA is filled with
> different values. The .Clear line works perfectly. So does = arEMA line,
> except all 1500 cells are filled with the same value - the first element
of
> arEMA.
>
> I don't want to have to loop through every cell, but it makes no sense to
me
> why this is happening.
>
> Any suggestions would be appreciated.
>
>
>

[Post Tenebras Lux]

Thank you. That worked. However, I don't understand why - I thought I had
defined a one dimensional VERTICAL array. It looked just like alot of other
code I've seen. Even when I looked at the array in the locals window and
watch window, they showed all the values in a one dimensional array.

So it must have been something wrong with the assignation to the range.
Again, it looked like every other assignation I've seen for a one dimensional
array. But if it works, I'm not complaining. I did have Options Base 1, so
maybe that somehow affected it.

Really appreciate the quick help.



"Peter T" wrote:

> You have defined a 1D horizontal array. If you were to do
>
> [a1:c1].value = arEMA
>
> the ROW should be populated with the first three values of your array
>
> Try it like this
>
> Redim arEMA(1 to rows),1 to cols)
>
> looks like you only have one column but include the second dimension when
> populating, eg
> arEMA(1,1) = first value
>
> Regards,
> Peter T
>
> "Post Tenebras Lux" <[email protected]> wrote in
> message news:[email protected]...
> > I have created an array that stores prices. When my code writes the
> values
> > of the array (single dimension, about 1500 elements), it writes the value
> of
> > the first element to every cell of the worksheet range. Relevant code is
> as
> > follows:
> >
> > Dim Px As Single, EMA_0 As Single, EMA_1 As Single
> > Dim arEMA() As Single
> >
> > nData = UBound(arPxData())
> > ReDim arEMA(nData)
> >
> > arEMA(1) = arPxData(1)
> > For i = 2 To nData
> > arEMA(i) = (2 / (1 + nPeriods) * (arPxData(i) - arPxData(i - 1)))
> +
> > arPxData(i - 1)
> > Next
> >
> > With Range("OutEMA0Head")
> > Range(.Offset(1, 0), .Offset(nData, 0)).Clear
> > Range(.Offset(1, 0), .Offset(nData, 0)) = arEMA
> > End With
> >
> > The Watch window shows that each element of the arEMA is filled with
> > different values. The .Clear line works perfectly. So does = arEMA line,
> > except all 1500 cells are filled with the same value - the first element
> of
> > arEMA.
> >
> > I don't want to have to loop through every cell, but it makes no sense to
> me
> > why this is happening.
> >
> > Any suggestions would be appreciated.
> >
> >
> >
>
>
>

[Peter T]

> However, I don't understand why - I thought I had
> defined a one dimensional VERTICAL array.

The point is it must define a 2 dimensional array if multiple rows are
involved, 1st-rows, 2nd-columns even if there is only one column.

Regards,
Peter T

"Post Tenebras Lux" <[email protected]> wrote in
message news:[email protected]...
> Thank you. That worked. However, I don't understand why - I thought I
had
> defined a one dimensional VERTICAL array. It looked just like alot of
other
> code I've seen. Even when I looked at the array in the locals window and
> watch window, they showed all the values in a one dimensional array.
>
> So it must have been something wrong with the assignation to the range.
> Again, it looked like every other assignation I've seen for a one
dimensional
> array. But if it works, I'm not complaining. I did have Options Base 1,
so
> maybe that somehow affected it.
>
> Really appreciate the quick help.
>
>
>
> "Peter T" wrote:
>
> > You have defined a 1D horizontal array. If you were to do
> >
> > [a1:c1].value = arEMA
> >
> > the ROW should be populated with the first three values of your array
> >
> > Try it like this
> >
> > Redim arEMA(1 to rows),1 to cols)
> >
> > looks like you only have one column but include the second dimension
when
> > populating, eg
> > arEMA(1,1) = first value
> >
> > Regards,
> > Peter T
> >
> > "Post Tenebras Lux" <[email protected]> wrote in
> > message news:[email protected]...
> > > I have created an array that stores prices. When my code writes the
> > values
> > > of the array (single dimension, about 1500 elements), it writes the
value
> > of
> > > the first element to every cell of the worksheet range. Relevant code
is
> > as
> > > follows:
> > >
> > > Dim Px As Single, EMA_0 As Single, EMA_1 As Single
> > > Dim arEMA() As Single
> > >
> > > nData = UBound(arPxData())
> > > ReDim arEMA(nData)
> > >
> > > arEMA(1) = arPxData(1)
> > > For i = 2 To nData
> > > arEMA(i) = (2 / (1 + nPeriods) * (arPxData(i) - arPxData(i -
1)))
> > +
> > > arPxData(i - 1)
> > > Next
> > >
> > > With Range("OutEMA0Head")
> > > Range(.Offset(1, 0), .Offset(nData, 0)).Clear
> > > Range(.Offset(1, 0), .Offset(nData, 0)) = arEMA
> > > End With
> > >
> > > The Watch window shows that each element of the arEMA is filled with
> > > different values. The .Clear line works perfectly. So does = arEMA
line,
> > > except all 1500 cells are filled with the same value - the first
element
> > of
> > > arEMA.
> > >
> > > I don't want to have to loop through every cell, but it makes no sense
to
> > me
> > > why this is happening.
> > >
> > > Any suggestions would be appreciated.
> > >
> > >
> > >
> >
> >
> >

[Post Tenebras Lux]

Ok - I think the error was not including the column reference when putting
values into arEMA. I now see that other code I had looked at did have the
second dimension reference, which was set to 0. Having set options base 1,
probably confused me about your arEMA9(nRows, 1).

Think I have it straight now.



"Peter T" wrote:

> You have defined a 1D horizontal array. If you were to do
>
> [a1:c1].value = arEMA
>
> the ROW should be populated with the first three values of your array
>
> Try it like this
>
> Redim arEMA(1 to rows),1 to cols)
>
> looks like you only have one column but include the second dimension when
> populating, eg
> arEMA(1,1) = first value
>
> Regards,
> Peter T
>
> "Post Tenebras Lux" <[email protected]> wrote in
> message news:[email protected]...
> > I have created an array that stores prices. When my code writes the
> values
> > of the array (single dimension, about 1500 elements), it writes the value
> of
> > the first element to every cell of the worksheet range. Relevant code is
> as
> > follows:
> >
> > Dim Px As Single, EMA_0 As Single, EMA_1 As Single
> > Dim arEMA() As Single
> >
> > nData = UBound(arPxData())
> > ReDim arEMA(nData)
> >
> > arEMA(1) = arPxData(1)
> > For i = 2 To nData
> > arEMA(i) = (2 / (1 + nPeriods) * (arPxData(i) - arPxData(i - 1)))
> +
> > arPxData(i - 1)
> > Next
> >
> > With Range("OutEMA0Head")
> > Range(.Offset(1, 0), .Offset(nData, 0)).Clear
> > Range(.Offset(1, 0), .Offset(nData, 0)) = arEMA
> > End With
> >
> > The Watch window shows that each element of the arEMA is filled with
> > different values. The .Clear line works perfectly. So does = arEMA line,
> > except all 1500 cells are filled with the same value - the first element
> of
> > arEMA.
> >
> > I don't want to have to loop through every cell, but it makes no sense to
> me
> > why this is happening.
> >
> > Any suggestions would be appreciated.
> >
> >
> >
>
>
>