array printing problems - transpose

[jlt199]

Hi,

I'm looping through some code building up a 1D array, which when 'full' (5000 elements) is sent to a function to determine which worksheet to paste it into and to then paste it.

My question is, is there any reason why this code should work

Please Login or Register  to view this content.

But this fails

Please Login or Register  to view this content.

Thanks

[shg]

I'd do it like this:

Please Login or Register  to view this content.

[jlt199]

Thanks, I tried your code, but it still kept falling over at

Please Login or Register  to view this content.

are there any limitations with the transpose function, such as length of string in each element, or length of array?

Thanks again

[shg]

Probably, but not at 5000 elements. What's the error?

[jlt199]

runtime error '13':

Type mismatch

I've tried several different tweaks to the code in the opening post, but keep getting this error.

[shg]

Post the rest of the code.

[jlt199]

Please Login or Register  to view this content.

I use very similar code with another array and it works well. The only thing I can think of is that the strings in each element of the array are long. But then printing each string individually works fine.

Thanks for your time

[shg]

How long?

Write some test cases. Shorten the array to fewer elements, and see if that works.

[jlt199]

Each element in the array will have around 200 comma seperated numbers.

I was wondering if this could be the problem (I seem to remember reading somewhere that each cell can only contain upto 250 characters) , but if I print each array element individually (code block one in my original post) it seems to work fine.

An example of the numbers being held are
60022, -0.001155, -0.002319, -0.003608, -0.004936, -0.006211, -0.007348, -0.008275

I've tried reducing the maximum number of elements in the array, but still not working.

[shg]

Cells can contain up to 32K characters, or which the first ~1K will display.

I may be (probably am) missing something simple. Can you post the most skinnied-down workbook possible that illustrates the problem?

[jlt199]

I've kept the function I'm having issues with as authentic as possible in case I am doing somethig stupid, and trimmed everything else as much as I could (It turns out that's quite difficult)

Many thanks

[shg]

OK, I can't look at this now, but will by and by if no one else sorts it out first.

[jlt199]

That's appreciated. Many thanks.

Jane

[shg]

You can only output 255 characters per array value in that fashion, which I checked like this:

Please Login or Register  to view this content.

Got a plan B?

For one thing, you could use Split to separate each line of the data at the commas, then you wouldn't need the text to columns afterward.

[jlt199]

Dang!!

Well thankyou for your time, I'll have to have a think.