Arrays to ranges on worksheet

[Lynn]

I have a VBA project that I am trying to make more efficient. One area I
know I could do something is writting to a range rather than cell by cell. I
am having an issue though. In particular, my array is dimensioned to be 100
elements (for other reasons I am not using this as a dynamic array). At any
one time, the array would only be partially full. I want to set the range
for the size of the partial array and write this partial array to that range.
Nothing is working for me.

Thanks in advance for any help you can give.

 

[james.igoe]

curious, do you need to set size of range? i'm a bit vague on your
needs, but some ideas...

dim iCounter as integer

'iterate through the array, writing values to range
for iCounter=0 to (ubound(array)-1)
.cells(iCounter+1,1) = array1(iCounter)
next

if you need to define range, e.g., as a named range, you could still
use the ubound of the array to set the size of the range. pardon the
less than correct code for the range.

if the array was discontinuous, you might use second array to write
non-null values into, using a similar counter arrangement, but with two
separate counters. Then use the second array for the range, or simply,
iterate through the array, count the non-nulls, set the rangge size,
then iterate through again, writng the non-null vaues to the range.

 

[Lynn]

My issue is that I do not want to loop through the cells in the range. I
want to be able to set my range equal to the array.

That is, say I have an array of 25 items, ArrayOfItems. If I have a range,
say RangeName, of 25 rows by 1 column, I want to be able to say that

RangeName = ArrayOfItems.

I thought I could do this but unfortunately, I am not getting it to work.
It continually just repeats the first item 25 times in the range.

 

[james.igoe]

I had more code that does something similar, but buried in old sheets,
but it is something like this:

Workbooks("Euro-Live.xla").Worksheets("Temp"). Range(.Cells(1, 1),
..Cells(lr1, 1)) = DataRange

DataRange is the array that I copy to an area. lr1 is a variable that
defines the range size. if you find the upper bound of the array and
use that as lr1, and the range is a column, the upperbound + 1 would be
the size of the range. it would also allow you to size the range as
well.

 

[Lynn]

Thanks for your help. I actually found my problem as I was trying to write
down the column and this will only write across columns. To do what I want
to do, it looks like I have to use the worksheet function transpose.

However, I have a problem with this because some of my elements of the
arrays are LONG strings that transpose does not seem to like.

Back to the drawing board.

If you have any other suggestions, I would appreciate hearing.

Thanks.