application.transpose, type mismatch

Hi All,

I'm using excel 2007 (but also excel 2000! ). I have imported a selection from a worksheet into an array and need to run some functions on the array such as the transpose function ( application.transpose(Arr) where Arr is a variant array). However it's comes up with a type mismatch error. I've narrowed the issue down to being the length of an array item being more than 255 characters long. As long as all entries in each cell that are to be imported are no more than 255 characters everything works fine. However I have to handle this scenario and so please could some one advise how best to do it.

Examples of the string I have to handle but the text could be anything really - I'd say to cpoe with 1000 characters would be plenty.

cell A1: Brand & other related char type changed to Asda Chosen By you & flavour changed to Smoky Barbecue. http://groceries.asda.com/asda-estor...requestid=4447

cell A2: Brand & other related char type changed to Asda Chosen By you & flavour changed to Smoky Barbecue. http://groceries.asda.com/asda-estor...questid=444767

cell A3: Brand & other related char type changed to Asda Chosen By you as well as flavour also changed to Chilli Cheese as per http://groceries.asda.com/asda-estor...questid=430427

Hope someone can help

Many thanks

Rodp

Originally Posted by JosephP

you'll need to create another array and loop through the first one transposing into the second

Hi JosephP,

Thanks for the reply. I thought about doing this but I fear that I'll hit other problems as there are many other excel / vba functions I call on in my complete routine which also might fall over due to this limit. Therefore is there a more permanent / flexible solution?

I thought splitting the array item in question to create a multiple dimension array but that just compounds the issue if you're then trying to do eg a simple vba function which you don't have the rights to access the underlying code.

Any other thoughts most welcome.

Thanks

RodP

Originally Posted by JosephP

you'd have to do something like that and then test every element you process to see if it is also an array then loop through it if need be

or upgrade to a better version of course ;-)

Hmmm - well that's a no goer then. Well we're unlikely to ungrade excel any time soon (it took 10 years to move from excel 2000 to 2007!!). If any one else comes up with a solution then do let me know.

Thanks

RodP