Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array data to Range: Only first value written
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array data to Range: Only first value written
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array data to Range: Only first value written
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array data to Range: Only first value written
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" wrote in message ... 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array data to Range: Only first value written
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Formula to get Range of Data to an Array | Excel Programming | |||
Q: Best way to take data from VBA into graphs without writing data to worksheets? (Can a named range refer to an array in memory only?) | Excel Programming | |||
Read Range Data into Array | Excel Programming | |||
Searching range for value (code written but needs 'tweaking'!) | Excel Programming | |||
How to protect single cell or cells range from being over-written? | Excel Programming |