Link to home
Start Free TrialLog in
Avatar of AL_XResearch
AL_XResearchFlag for United Kingdom of Great Britain and Northern Ireland

asked on

VBA Transpose and Excel 2010

I am trying to use 
targetRange = Application.transpose(myArray)

Open in new window

to output a string array directly to multiple worksheet rows.

Has this function been removed from that version of Excel as I can't find it as a method of the Application object?
SOLUTION
Avatar of [ fanpages ]
[ fanpages ]
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Why didn't I think of that !! Never occurred to me to look under worksheet functions !

I am sure that you used to have a [Application.transpose] command though.
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
Application.Transpose still works (like the other WorksheetFunction methods). It was deprecated a long time ago.
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

I hate to differ but 'Application.Transpose' is not available in Excel 2010 (at least it is not on intellisense),

Application.WorksheetFunction.Transpose is no use. I need to transpose as (or before) I apply the values to the sheet. The worksheet function requires that you already have a range of values on the sheet and these are transposed.

What I need is to convert a text array from horizontal to vertical so that when I apply it to a range the values are stored vertically and not (as is default) in a horizontal format.
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
It is still available. It hasn't shown up in Intellisense for years.

Neither version requires a Range object - they both work with arrays. They are limited in the size of text they can cope with and the size of array. If you would like an alternative, you can use a virtual Listbox: http://excelmatters.com/?p=27
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

[Application.Transpose] does not work in 2010 - just errors saying 'unknown function'

The worksheetfunction equivalent does not transpose a text array. I have tried it.
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
Both of those statements are incorrect, I'm afraid - I use application.transpose frequently in all versions and both of the transpose methods work with text, although they can have issues with text over 911 characters.
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Can you post your example code because doing a [Application.Transpose] did nothing to re-format my string array.

I can get the WorksheetFunction transpose to work if I return the result to a range but again it does not work with a string array
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

rorya : Thank you very much - that explains why I couldn't get it work. I was expecting the transpose function to change the nature of the array it was passed (i.e. the dimensional definition) for later use. In your example the transposed array is a re-structured copy that is returned into the array.

Thanks alot.