+ Reply to Thread
Results 1 to 14 of 14

To transpose or not to transpose? What decides? Table / Array?

  1. #1
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    To transpose or not to transpose? What decides? Table / Array?

    Hi all, here is the code suggested to me (by nigelog with the aid of jindon) from a previous thread I posted. As I'm still just learning VBA I am trying to make sure I understand all aspects of it.

    Please Login or Register  to view this content.
    My question is about the Application.Transpose, in another thread " What to avoid in VBA" the following code line is written by shg:

    Please Login or Register  to view this content.
    So why does the code need the transpose, and what decides it?
    Do I absolutely need an array in that code if I'm working within a table? (listobject) (It is important to refer to the columns by their headers!! They are also not adjacent.)

    Also what does the 0 "decides" after the .Value, what would change if it was 1 in the code line?

    I got a few answers so far saying about 1 dimensional and 2 dimensional vectors.. but I think I need this a bit more "visually".. I can see that Range ("A1:G1") is a single line (1 dimensional?), but the way I see it listrow(i) is also just a single line (1 dimensional?)? Or is it because its not every value I need concatenated from listrow(i) only 3 of them from 3 not adjacent columns?
    Does x really need to be declared and the array defined, could it not be that instead of x in the "DictKey=" line we would just write the columns identified by their headers? Or is it 2 dimensional BECAUSE I'm identifying them by their headers? As it can't just count from left to right it has to look up to the top of the table to check the column header?

    Can somebody help to clear this up for me?

    Thank you!

    Links:
    My previous thread: https://www.excelforum.com/excel-pro...al-failed.html
    "What to avoid in VBA" thread: https://www.excelforum.com/excel-pro...in-vba-11.html

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    3,158

    Re: To transpose or not to transpose? What decides? Table / Array?

    The double transpose isn't actually necessary there.

    The 0 in shg's code is to indicate that all the columns should be returned. If you replaced it with 1, you'd just get the value from the first column, which is not an array so the code would fail.
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  3. #3
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: To transpose or not to transpose? What decides? Table / Array?

    If I write it like this:

    Please Login or Register  to view this content.
    I get Run-time error 1004 invalid number of arguments?

    If I leave the x array as it is but remove the transpose it works.. so I guess it wasn't necessary, but still. Why? And why x array is necessary?

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    3,158

    Re: To transpose or not to transpose? What decides? Table / Array?

    You need an array because you want multiple columns returned (the array is the column numbers).

    The double transpose is not necessary here because the Index part already achieves the desired result of creating a 1D array.

  5. #5
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: To transpose or not to transpose? What decides? Table / Array?

    So basically tricking Excel to return 1 object.. which consists of 3 columns.. because it can't return 3 objects which would be the 3 columns?

    Achieves the desired 1D array because of the .Listrow(i)?

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,667

    Re: To transpose or not to transpose? What decides? Table / Array?


  7. #7
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: To transpose or not to transpose? What decides? Table / Array?

    oh wow that version doesn't even have Application.Index in it anymore.. maan if its possible to get even more confused

    anyway I presume its safe to conclude, Join(Array .... is more powerful than Join(Application.Index .... ?

    cause in that case I will just abandon Join(Application.Index .... completely...

    I've just posted such lines just because you wanted to rewrite your original code without showing what you are trying to do.

    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    1,724

    Re: To transpose or not to transpose? What decides? Table / Array?

    You will find that
    Please Login or Register  to view this content.
    also needs to be transposed. Jindon set you in the right direction.

    Also, Mind the two "Surnames" in the code above.
    Please Login or Register  to view this content.
    Last edited by nigelog; 02-15-2019 at 08:12 AM.

  9. #9
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: To transpose or not to transpose? What decides? Table / Array?

    Quote Originally Posted by nigelog View Post
    You will find that
    Please Login or Register  to view this content.
    also needs to be transposed.
    What needs to be transposed? I tried the original code suggested by you (with the help of jindon) and it worked without the transposes in it...

  10. #10
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: To transpose or not to transpose? What decides? Table / Array?

    One more question:

    What does the 1 stand for in this line? What would change if it was 0?

    Please Login or Register  to view this content.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,667

    Re: To transpose or not to transpose? What decides? Table / Array?

    Range(RowIndex, Colum Index) based on ListRows.

    Just try change the value for yourself.

  12. #12
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: To transpose or not to transpose? What decides? Table / Array?

    I thought row index in this case was i?

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,667

    Re: To transpose or not to transpose? What decides? Table / Array?

    No, it should be 1 ONE.

    I said Row/Column Index BASED on ListRows(), means Range(Row/Column) from ListRows(i).
    1 refers to the row that is the same row, 2 refers to one row down from the ListRows(i).

  14. #14
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: To transpose or not to transpose? What decides? Table / Array?

    Oh okay thats something new I learned from you again!
    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1