+ Reply to Thread
Page 4 of 10 FirstFirst ... 2 3 4 5 6 ... LastLast
Results 46 to 60 of 136

VBA Transpose

  1. #46
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: VBA Transpose

    That will certainly assist. In addition, in your CopySheet1A1toSheet2NextCellInColumnA procedure, you should loop from row 2, not from row 1, since that row contains headers rather than data.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  2. #47
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Transpose

    is that this bit?

    'Loop through all used cells on column A with a value
    For l = 1 To lLastRow

  3. #48
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: VBA Transpose

    That's correct- you need to start at row 2.

  4. #49
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Transpose

    Should it look like this? sorry for asking so many questions

    'Loop through all used cells on column A with a value
    For l = 2 To lLastRow

  5. #50
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: VBA Transpose

    Yes it should.

  6. #51
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Transpose

    for some reason it is still showing as an error

  7. #52
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: VBA Transpose

    Please post a sample workbook again with the corrected code and data without error values

  8. #53
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Transpose

    would it affect the code if the amount of data has changed?

  9. #54
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: VBA Transpose

    It shouldn't- which line is in error now?

    Unless perhaps you have too many items and run out of columns.

  10. #55
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Transpose

    well for the moment it is working okay. but I have yet to run the code which sorts all of the values into the named ranges.

  11. #56
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Transpose

    Hi,

    So I have tried to Loop it from Row 2 but it is just not working. the transpose code is perfect has set it all up perfectly and without creating duplicates but the sorting code just refuses to work and i am sure it is something really obvious I am missing
    Attached Files Attached Files

  12. #57
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: VBA Transpose

    Hello again,

    I presume you are referring to the CopySheet1A1toSheet2NextCellInColumnA procedure? If so, could you explain what it is intended to do?

  13. #58
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Transpose

    Sort Columns B & C into the named ranges on sheet 2

  14. #59
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: VBA Transpose

    The code should read thus

    Please Login or Register  to view this content.
    but this approach will be quite slow I imagine. It would be better to collate the data first and then write it out to the relevant ranges. I have to go into a board meeting shortly but will endeavour to post that approach later today.

  15. #60
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Transpose

    what I also find troubling is that when I run "ConcatenateABandRemoveAllValueswhichwillerror " to prepare the values it wont transpose the range and gives me a mismatch error but it is identical to the values that it requires.

+ Reply to Thread
Page 4 of 10 FirstFirst ... 2 3 4 5 6 ... LastLast

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