+ Reply to Thread
Results 1 to 5 of 5

Transposing Excel table using VBA

  1. #1
    Registered User
    Join Date
    06-27-2018
    Location
    CA
    MS-Off Ver
    2012
    Posts
    6

    Transposing Excel table using VBA

    Hi all,

    I am super new to VBA and have looked at trying to fix this code on my own for a while now. I am trying to use the below code to transpose a table in excel. The table has 522 rows and 2 columns. I need it to be transposed to 522 columns and 2 rows.

    Capture.JPG
    Please Login or Register  to view this content.
    what am I doing wrong?

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Transposing Excel table using VBA

    Hi there,

    See if the following code does what you need:

    Please Login or Register  to view this content.
    The highlighted values may be altered to suit your own requirements.


    The mistake in your own code is in the line:

    Please Login or Register  to view this content.
    x is declared as a Long variable, but CurrentRegion is returning a multi-cell range, so assigning the Value property of this range (i.e. an array of values) to a Long variable will produce a Type Mismatch error.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Transposing Excel table using VBA

    x is declared as a Long variable
    Nah... It (and the y variable) are declared as a Variants..

    To debug that.. perhaps either upload a sample file or at least show the line the VBA debugger shows is in error..
    Last edited by apo; 06-28-2018 at 11:10 AM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Transposing Excel table using VBA

    Please Login or Register  to view this content.
    Or just
    Please Login or Register  to view this content.
    Last edited by jindon; 06-28-2018 at 11:26 AM.

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Transposing Excel table using VBA

    Definitely my bad

    Please Login or Register  to view this content.
    x and y are (by default) declared as Variants - that's why I NEVER use Dim statements like that!


    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. transposing a table
    By samyraj in forum Excel General
    Replies: 5
    Last Post: 03-16-2016, 11:39 AM
  2. Transposing and consolidating a 1d to a 2d table
    By danberg2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-09-2013, 05:04 PM
  3. [SOLVED] Transposing table
    By justMartin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-11-2012, 03:29 PM
  4. Transposing data from one big list into a table
    By kolson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-10-2010, 06:03 PM
  5. Transposing a table view
    By ElmerS in forum Excel General
    Replies: 11
    Last Post: 10-31-2009, 04:45 AM
  6. Transposing a table
    By ElmerS in forum Excel General
    Replies: 9
    Last Post: 07-14-2009, 03:00 PM
  7. Transposing a table
    By KH_GS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-22-2007, 12:10 PM

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