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

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

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

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"
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

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

On Sep 21, 4:47 pm, "Jim Cone" wrote:
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"
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

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

On Sep 21, 6:25 pm, eholz1 wrote:
On Sep 21, 4:47 pm, "Jim Cone" wrote:



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"
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

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

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

this populates dataArray for me

Set myrange = Range("C2:C6")

dataArray = myrange

--


Gary


"eholz1" wrote in message
oups.com...
On Sep 21, 6:25 pm, eholz1 wrote:
On Sep 21, 4:47 pm, "Jim Cone" wrote:



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"
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

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

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

On Sep 21, 7:17 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this populates dataArray for me

Set myrange = Range("C2:C6")

dataArray = myrange

--

Gary

"eholz1" wrote in message

oups.com...

On Sep 21, 6:25 pm, eholz1 wrote:
On Sep 21, 4:47 pm, "Jim Cone" wrote:

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"
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

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

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