Strange pasting array to cells behavior
 

First off, if you know an easier way to fill a 1-D array with a 1-D range's
values, then add to it from another 1-D range, please let me know. Someone
posted that ArrayName = Range("A1:A5") would put those 5 values in the
array, but it was not working for me. What I'm doing is assigning specified
range of cell's values to an array, then assigning another specified range'
values to it aftr a little more operation. My problem is with my first
assignment, however:

I have this:

dim ElementList() as variant

StartRange = Range(FoundIt.Address).Offset(3, -2).Address
EndRange = Range(StartRange).End(xlDown).Address

I've already done some variable assignments, you can trust that these ranges
being assigned is working; StartRange is A8, EndRange is A11

I then populate ElementList array with the values of the cells From A8 to
A11:

For Each Cell In Range(StartRange, EndRange)
ReDim Preserve ElementList(0 To i)
ElementList(i) = Cell.Value
i = i + 1
Next Cell

Now, as I'm looping through the cells in this range, I can debug.print each
value; so the array is indeed getting all the cell values. HOWEVER, when I
then execute this line:

Worksheets("Sheet1").Range("C3:C" & UBound(ElementList) + 1) =
Application.WorksheetFunction.Transpose(ElementLis t)

I only get 2 of the items in the array. When I change the "+1" to "+3" I
get all of them. Why would my range that I'm filling with my array have to
be 3 cells bigger than the number of values in my array in order to get all
the values? There are no blanks in the array, when I try to debug.print(4)
I get subscript out of range, because there are only 0 through 3 items in
there.

Can anyone explain why this is happening? And please, remember, if there's
an easier way to assemble an array with a range's cell values and then
output it to a column, I'm all ears. Big time.

Thanks for reading

Strange pasting array to cells behavior
 

'---Part One
The upperbound of ElementList is 3
3 +1 = 4
So...
("C3:C" & UBound(ElementList) + 1)
Translates to ...
("C3:C4")

'--- Part Two
Dim vSludge as Variant
vSludge = Range(StartRange, EndRange).Value

vSludge is now a variant containing an array.
You treat it exactly like you would a regular array.
Also it is a 1 based array containing 4 rows and one column.
vSludge(1, 1) should contain the value in A8
vSludge(4, 1) should contain the value in A11
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(review of my excel sorting add-in)

..
..
..

"CompleteNewb"

wrote in message
...
First off, if you know an easier way to fill a 1-D array with a 1-D range's
values, then add to it from another 1-D range, please let me know. Someone
posted that ArrayName = Range("A1:A5") would put those 5 values in the
array, but it was not working for me. What I'm doing is assigning specified
range of cell's values to an array, then assigning another specified range'
values to it aftr a little more operation. My problem is with my first
assignment, however:

I have this:

dim ElementList() as variant

StartRange = Range(FoundIt.Address).Offset(3, -2).Address
EndRange = Range(StartRange).End(xlDown).Address

I've already done some variable assignments, you can trust that these ranges
being assigned is working; StartRange is A8, EndRange is A11

I then populate ElementList array with the values of the cells From A8 to
A11:

For Each Cell In Range(StartRange, EndRange)
ReDim Preserve ElementList(0 To i)
ElementList(i) = Cell.Value
i = i + 1
Next Cell

Now, as I'm looping through the cells in this range, I can debug.print each
value; so the array is indeed getting all the cell values. HOWEVER, when I
then execute this line:

Worksheets("Sheet1").Range("C3:C" & UBound(ElementList) + 1) =
Application.WorksheetFunction.Transpose(ElementLis t)

I only get 2 of the items in the array. When I change the "+1" to "+3" I
get all of them. Why would my range that I'm filling with my array have to
be 3 cells bigger than the number of values in my array in order to get all
the values? There are no blanks in the array, when I try to debug.print(4)
I get subscript out of range, because there are only 0 through 3 items in
there.

Can anyone explain why this is happening? And please, remember, if there's
an easier way to assemble an array with a range's cell values and then
output it to a column, I'm all ears. Big time.

Thanks for reading

Strange pasting array to cells behavior
 

Thanks for the help, Jim. It wasn't clicking in my mind that I was using
UBound to set the range's last cell's row number.

Sorry for the silly oversight, and thanks again.


"Jim Cone" wrote in message
...

'---Part One
The upperbound of ElementList is 3
3 +1 = 4
So...
("C3:C" & UBound(ElementList) + 1)
Translates to ...
("C3:C4")