Writing array data to range object always writes 0's on worksheet

[eholz1]

Hello Excelers!

I have a program that subtracts time values on a worksheet and is
SUPPOSED to write the diff
to a worksheet row (same column).

I define a range object for the data (the times to subtract) =
data(row 2) - data(row 1) etc.
I then write the diff values into an array (variant)

I can see values in the array in the locals window

My problem is when I attempt to write the new array with the diff
values in to a range object (new column, and x rows) It always puts
zeros in the range on the worksheet, but the correct number of rows
and the
correct column.

Here is the code that takes the data, and puts it into the range (i
wish):

Set outputRange = Range(ColNum.Offset(0, 2), ColNum.Offset(0, 2))

rngRow = outputRange.Address
rngCol = UBound(a)

For k = LBound(a) To UBound(a)
Debug.Print a(k)
Next k

outputRange.Value = a ' a is the array with the data in it (time
values like 00:10:00 - 00:8:00)

' the output range is C3:C6 (this can change)

I write the subtractions (values) into a (a variant) Debug prints the
correct data.
I redim the array to the count of rows (in this case 5)

I always get zeros - what am i missing here?
i choose not to do writes row by column because the dataset can be
really big -

any suggestions???

thanks
eholz1

 

[Jim Cone]

Some thoughts...
C3:C6 consists of four cells not five.
Add Option Explicit to your module.
Remove On Error Resume Next.
I am guessing that you have a horizontal array and the range
receiving the data is a single column. You would have to
transpose the array.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"eholz1" <[email protected]>
wrote in message
Hello Excelers!
I have a program that subtracts time values on a worksheet and is
SUPPOSED to write the diff
to a worksheet row (same column).

I define a range object for the data (the times to subtract) =
data(row 2) - data(row 1) etc.
I then write the diff values into an array (variant)
I can see values in the array in the locals window
My problem is when I attempt to write the new array with the diff
values in to a range object (new column, and x rows) It always puts
zeros in the range on the worksheet, but the correct number of rows
and the correct column.
Here is the code that takes the data, and puts it into the range (i
wish):

Set outputRange = Range(ColNum.Offset(0, 2), ColNum.Offset(0, 2))
rngRow = outputRange.Address
rngCol = UBound(a)
For k = LBound(a) To UBound(a)
Debug.Print a(k)
Next k
outputRange.Value = a ' a is the array with the data in it (time
values like 00:10:00 - 00:8:00)

' the output range is C3:C6 (this can change)
I write the subtractions (values) into a (a variant) Debug prints the
correct data.
I redim the array to the count of rows (in this case 5)

I always get zeros - what am i missing here?
i choose not to do writes row by column because the dataset can be
really big -
any suggestions???
thanks
eholz1

 

[eholz1]

Some thoughts...
C3:C6 consists of four cells not five.
Add Option Explicit to your module.
Remove On Error Resume Next.
I am guessing that you have a horizontal array and the range
receiving the data is a single column. You would have to
transpose the array.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"eholz1" <[email protected]>
wrote in message
Hello Excelers!
I have a program that subtracts time values on a worksheet and is
SUPPOSED to write the diff
to a worksheet row (same column).

I define a range object for the data (the times to subtract) =
data(row 2) - data(row 1) etc.
I then write the diff values into an array (variant)
I can see values in the array in the locals window
My problem is when I attempt to write the new array with the diff
values in to a range object (new column, and x rows) It always puts
zeros in the range on the worksheet, but the correct number of rows
and the correct column.
Here is the code that takes the data, and puts it into the range (i
wish):

Set outputRange = Range(ColNum.Offset(0, 2), ColNum.Offset(0, 2))
rngRow = outputRange.Address
rngCol = UBound(a)
For k = LBound(a) To UBound(a)
Debug.Print a(k)
Next k
outputRange.Value = a ' a is the array with the data in it (time
values like 00:10:00 - 00:8:00)

' the output range is C3:C6 (this can change)
I write the subtractions (values) into a (a variant) Debug prints the
correct data.
I redim the array to the count of rows (in this case 5)

I always get zeros - what am i missing here?
i choose not to do writes row by column because the dataset can be
really big -
any suggestions???
thanks
eholz1

Hello again,

I made a mistake on my range it is "C2:C6"
I did not show all the code - in the module i have OPTION EXPLICIT
I do not have On Error Resume Next

I will take another look, and send more code.
I define outputRange as a Range
I define the array "a" as Dim a() as Variant

Thanks,

eholz1

 

[eholz1]

Hello again,

I made a mistake on my range it is "C2:C6"
I did not show all the code - in the module i have OPTION EXPLICIT
I do not have On Error Resume Next

I will take another look, and send more code.
I define outputRange as a Range
I define the array "a" as Dim a() as Variant

Thanks,

eholz1

Hello Forum,

I have taken another look.
It seems that trying to write values from an array (defined as
variant) to a defined range on a worksheet does not work for
some strange reason. I tried a Sheet1.Cells(rownum,colnum) in a for
loop for the array - that will write the data to the sheet.

But setting a range: Set myRange = Range(C2:C6)
and then doing: myRange.Value = dataArray writes only zeros to the
sheet.

Any more thoughts on this?

eholz1

 

[Gary Keramidas]

this populates dataArray for me

Set myrange = Range("C2:C6")

dataArray = myrange

 

[eholz1]

this populates dataArray for me

Set myrange = Range("C2:C6")

dataArray = myrange

Hello All,

I finally figured it out. I forgot that when writing to a range - it
needs to be an "array variable" aka matrix.
I was defining my array "a" as ReDim a(5) (elements) WRONG - correct
dim is Redim a(i,,j) where i represents rows, and j represents
columns. In my case only 1 column with "x" rows
so the correct statment is: Redim a(1 to x, 1 to 1) - I then wrote
the data in, and then
outputRange.Value = a, works.

Thanks for the help,

eholz1