Macro won't transpose my array

[georgieporgie]

Hi all,

I created a macro that loops through some sensitive documents in order to create an array. However, when I try to transpose my array back into excel I'm receiving Error 13: Mismatch error.

I've provided some images for clarification. Is there something wrong with my code? Any help is much appreciated.

Thanks,

G

[AB33]

The range object seems to me in incorrect.
Most likely.

Please Login or Register  to view this content.

[georgieporgie]

@AB33 Thanks for responding! I'm still having some troubles even with your line of syntax. That's why I'm confused because the range object is actually correct. I'm transposing an array of 10 rows, 5706 columns so I'm not sure why my syntax and yours won't work. If you need me to submit additional information please let me know.

[AB33]

On my test it works even if you flip flop the boundaries. About 7000 columns and 10 rows

Please Login or Register  to view this content.

[georgieporgie]

Hmm.. that is strange. Do you know what else might be affecting it? I'm still receiving the type mismatch Error 13.

[AB33]

It is difficult to pin point as to why, but if you could an excel sample book with the code, I might see it.
I know Transpose has a limit of 65k rows, but you have not reached the limit.
Just before you write back to excel you could try

Debug.Print newarray
and see if there is an error.

[georgieporgie]

Debug.Print newarray provides me with a compile error: Type Mismatch.

I'll see if I can provide a copy of a sample book.

[AB33]

In that case, the issue is with newarray, not transferring the array in to sheet.

[georgieporgie]

I'm not entirely sure about that either because I have tried transferring newarray to my sheet WITHOUT transposing the information and the result is perfect. Not only that, but if I manually transpose the range onto the correct sheet, I am getting the results I want the macro to achieve autonomously.

Have a look at the workbook and tell me what you think could be causing this.

[georgieporgie]

I'm not entirely sure about that either because I have tried transferring newarray to my sheet WITHOUT transposing the information and the result is perfect. Not only that, but if I manually transpose the range onto the correct sheet, I am getting the results I want the macro to achieve autonomously.

Have a look at the workbook and tell me what you think could be causing this.

[AB33]

I can not test it as you have two different workbooks and many sheets, but I am also not surprised you are getting an error. There can only be one active workbook at a time. If the code works on a single book, the error can only attribute to working on two books.
This is my idea. Instead of opening two books at a time. Open one workbook, load the array and close it and you then work on a single book. Unless you are using range, you do not need to open two books. Once you loaded the data, do not refer it on your code