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?
LVL 3
AL_XResearchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

How about...

Application.WorksheetFunction.Transpose(...)

i.e.

targetRange = Application.WorksheetFunction.Transpose(myArray)

?

BFN,

fp.
0
AL_XResearchAuthor Commented:
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.
0
Rory ArchibaldCommented:
Application.Transpose still works (like the other WorksheetFunction methods). It was deprecated a long time ago.
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

AL_XResearchAuthor Commented:
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.
0
Rory ArchibaldCommented:
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
0
AL_XResearchAuthor Commented:
[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.
0
Rory ArchibaldCommented:
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.
0
AL_XResearchAuthor Commented:
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
0
Rory ArchibaldCommented:
Very simple example:
Sub DumpArray()
   Dim asTemp(9) As String
   Dim n As Long
   For n = 1 To 10
      asTemp(n - 1) = "Test item " & n
   Next n
   
   Range("A1:A10").Value = Application.Transpose(asTemp)
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AL_XResearchAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.