+ Reply to Thread
Results 1 to 136 of 136

VBA Transpose

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

    VBA Transpose

    Hi Guys,

    I have been using the below code to transpose some order numbers

    Please Login or Register  to view this content.
    What I want to know if it is possible to transpose the order numbers into everyother cell?

    many thanks

    Jamie
    Last edited by alansidman; 09-14-2016 at 10:58 AM. Reason: code tags added

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

    Re: VBA Transpose

    Hi,

    There are many possible ways- for instance

    Please Login or Register  to view this content.
    Last edited by xlnitwit; 09-14-2016 at 12:09 PM.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

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

    Re: VBA Transpose

    Thats awesome thank you so much ^_^

    could I ask you a question about naming those copied values

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

    Re: VBA Transpose

    Certainly- what is it?

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

    Re: VBA Transpose

    is there a way that you could name those copied ranges based on the value that has just been copied.

    so say you copied 1234 onto sheet 2 B2. could you then name the range b2:c3 using the value you have just copied.

    does that make sense?

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

    Re: VBA Transpose

    Yes- but 1234 is not a valid name for a range. Are the copied values in fact numbers?

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA Transpose

    Code Tags Added
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    Re: VBA Transpose

    my appologies will remember for next time

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

    Re: VBA Transpose

    some will be numbers and some will be text. is there anyway around it? like adding a letter to the start of the account number or something?

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

    Re: VBA Transpose

    It would be possible to add an underscore which should make it a valid name?

    Please Login or Register  to view this content.
    Last edited by xlnitwit; 09-14-2016 at 12:09 PM.

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

    Re: VBA Transpose

    got it

    could you transpose column A:B from sheet 1 on To Sheet 2 and name the same range b2:c3 with the first value


    JohnsTyres 1234 and the range would be named Johns tyres

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

    Re: VBA Transpose

    its coming up with a runtime error 13 mismatch

    [OutRange.Resize(1, 2).Name = "_" & InRange.Value2}

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

    Re: VBA Transpose

    Whoops! I will correct that forthwith. Did you mean B2:C3 or B2:C2 as the range to name?

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

    Re: VBA Transpose

    B2:C3 and thank you really appreciae your help

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

    Re: VBA Transpose

    In that case this should do the job

    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    Thank you ^_^

    Unfortuantly its now coming up with a runtime error 1004

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

    Re: VBA Transpose

    Morning,

    Which line is causing the error?

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

    Re: VBA Transpose

    its saying object required but i have checked and all of the Ref's are correct.

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

    Re: VBA Transpose

    Are you certain that the code names for your worksheets are Sheet1 and Sheet2? Code names are not necessarily the same as the text shown on the worksheet tab.

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

    Re: VBA Transpose

    so I have checked all of the references and they are fine. when I run the code it copies the first value from sheet 1 and then stops

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

    Re: VBA Transpose

    Sorry for being a pain xlnitwit

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

    Re: VBA Transpose

    Simply stops without error?

    I belatedly created a test workbook this morning and ran the code on it without any errors. Is it possible for you to attach your workbook here?

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

    Re: VBA Transpose

    Hi,

    I have attached the demo workbook I am using.
    Attached Files Attached Files

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

    Re: VBA Transpose

    The problem is that you have duplicate rows in your source data and you cannot give two ranges the same name. How would you like to handle that?

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

    Re: VBA Transpose

    Remove the duplicates

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

    Re: VBA Transpose

    I have removed the duplicates and the same error is still occuring.

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

    Re: VBA Transpose

    The following should work with that dataset- fixing the duplicate issue as well as coping with characters that are not valid in a name, which will be replaced with an underscore

    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    Hi,

    I have been playing around with the above for ages and cant quite work out what I would need to amend for it to copy A,B & C instead of just A & B can you help me?

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

    Re: VBA Transpose

    Change to this

    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    I have just tried to copy this over and it is coming up with a compile error sub or function not defined

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

    Re: VBA Transpose

    Did you keep the other function code?

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

    Re: VBA Transpose

    So I amended the code as advised but it is still only transposing A:B

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

    Re: VBA Transpose

    Can you post a workbook including the code as you are using it at the moment please?

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

    Re: VBA Transpose

    Here you go
    Attached Files Attached Files

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

    Re: VBA Transpose

    That file is missing an important ingredient- the code as you are using it at the moment please.

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

    Re: VBA Transpose

    my appologies please see attached
    Attached Files Attached Files

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

    Re: VBA Transpose

    Oh dear- I'm afraid I overlooked a crucial change! Please use this

    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    Thats awesome thank you so much. if I was to use this for text as well is what can i remove to remove the underscore?

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

    Re: VBA Transpose

    On the assumption you mean the leading underscore only, remove the red section here
    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    Hi XLNIT WIT,

    Could i have some help please as I feel as though I am about to cry LOL

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

    Re: VBA Transpose

    With what do you need help?

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

    Re: VBA Transpose

    Hi,

    So i have utilised all of the code over the last couple of days to make the attached workbook. What should happen is

    1. Is that ConcatenateABandRemoveAllValueswhichwillerror Should run through sheet 1 and create the names for soon to be ranges on sheet2.

    2. part of this also removes any random values such as (., spaces, brackets and so on) so as not to cause any conflicts.

    3. then it should use the values in A1 to create ranges on sheet2

    4. and then copy cells D & E into the range defined in A

    5. But it keeps on erroring and I dont know why, as everything that i have put in place should ensure that the ranges dont error.
    Attached Files Attached Files
    Last edited by bloodmilksky; 09-22-2016 at 08:18 AM. Reason: wrong workbook uploaded

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

    Re: VBA Transpose

    Have you tried debugging the code by stepping through it with f8? If you were to do so, you will likely see that it fails in many places but is certainly not helped by the fact that column B consists solely of error values.

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

    Re: VBA Transpose

    I have amended colum B as it should show all the Email Addy's. I am just awaiting for more information to be able to get the rest of them together.

    Would you know how I would fix this.

    the one I have attached now shows some of the emails that are their.

    but still isnt creating the ranges.

    I am sorry to ask so many questions but I am very new to VB and am just running on the little knowledge I have.
    Attached Files Attached Files

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

    Re: VBA Transpose

    would it matter if I changed the error values to just a dummy email just to confirm that It can actually work?

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

    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.

  47. #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

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

    Re: VBA Transpose

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

  49. #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

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

    Re: VBA Transpose

    Yes it should.

  51. #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

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

    Re: VBA Transpose

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

  53. #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?

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

    Re: VBA Transpose

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

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

  55. #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.

  56. #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

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

    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?

  58. #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

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

    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.

  60. #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.

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

    Re: VBA Transpose

    thank you I really appreciate your help sorry for being so much trouble.

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

    Re: VBA Transpose

    Not at all- I do this to keep my hand in with Excel so it's all useful to me!

    Here is an example of my suggested approach

    Please Login or Register  to view this content.
    In truth, it would be better to combine this with the step that names the ranges in the first instance as there is a lot of overlap in the processing the two procedures do.

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

    Re: VBA Transpose

    I think alot of the problem I am having is because I have seperated

    ConcatenateABandRemoveAllValueswhichwillerror - Transposerange - CopySheet1A1toSheet2NextCellInColumnA


    but I myself wouldnt even know where to begin with trying to merge these. everytime I have one part working the others dont. I am not ashamed to say it is making me a little bit emotional.

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

    Re: VBA Transpose

    Do you require them to be separated for any reason?

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

    Re: VBA Transpose

    not at all just dont have the knowledge to run them all as one.

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

    Re: VBA Transpose

    I doubt I will have the time today but I will try to consolidate them for you tomorrow at some stage.

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

    Re: VBA Transpose

    thats great thank you I really appreciate your help on this

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

    Re: VBA Transpose

    Hey Xlnitwit Did you have any lucky? or should I go back to the drawing board?

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

    Re: VBA Transpose

    I'm afraid I did not have the time yesterday but will see what I can do later this afternoon once I have some meetings out of the way.

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

    Re: VBA Transpose

    thank you once again really appreciate it.....just didnt know if it was a lost cause LOL

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

    Re: VBA Transpose

    Can you confirm which data should be brought across in the last routine? I know you have said columns C and D, but column D is empty in your last example file so I would like to confirm it is the correct one.

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

    Re: VBA Transpose

    My appologies I think I may have got the ranges mixed up

    So after the ranges have been set up on sheet 2 the below should be left ready to be sorted in to them. and its only Col B & C thats going into these defined ranges.
    COL A COL B COL C
    _LionDooE10161 LFRQ1DSI87-0800 w/c 19/09
    _AlBarakatGroupCoLtdSaudiE10048 LBMDTRC387+0475075010 w/c 19/09
    _DandAContactLensesLimitedDA0002 LOPTN1EI88+0600 w/c 26/09


    Thank you so much for the help

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

    Re: VBA Transpose

    That makes more sense. Can you provide a sample workbook as it is at the start and indicate which macros you run and in which order?

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

    Re: VBA Transpose

    So it should go:

    1. ConcatenateABandRemoveAllValueswhichwillerror

    2. then it should sort the Item Codes and Dates using... CopySheet1A1toSheet2NextCellInColumnA ( but it weirds out for some reason)
    Last edited by bloodmilksky; 10-03-2016 at 11:08 AM.

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

    Re: VBA Transpose

    it wont let me upload the file as it is too big

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

    Re: VBA Transpose

    got it with some adjustments
    Attached Files Attached Files

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

    Re: VBA Transpose

    Did you manage to get the file okay?

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

    Re: VBA Transpose

    I had not seen that message until now. I will download it and have a look this morning.

    Is that the file as it will be before running the two routines you mention, or before running any of the code in the file?

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

    Re: VBA Transpose

    yes excluding the VLOOKUP i use to get the email addresses.

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

    Re: VBA Transpose

    That was intended as an either/or question. Is that file as it will be before you run any code at all, or as it will be just before you run the two routines we are talking about?

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

    Re: VBA Transpose

    before any code has been run at all

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

    Re: VBA Transpose

    OK, and you just run those two routines, or does any other code need to be run as well? At first glance it would appear more code is needed to put the data where I think it ought to be.

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

    Re: VBA Transpose

    I think everything should be in the first routines. i think :S then it will just be emailing of those ranges

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

    Re: VBA Transpose

    After running the first routine, Sheet1 has the following data:
    Column A: Business PartnerID
    Column B: email addresses
    Column C: blank
    Column D: blank
    Column E: item code
    Column F: Order date.

    Is that how it should look? That doesn't seem to match the situation that has been described for the second routine.

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

    Re: VBA Transpose

    sorry i must have missed something because it should only be on D left blank and then E&F should be sorted into the defined ranges

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

    Re: VBA Transpose

    Did you need me to supply you with any further information

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

    Re: VBA Transpose

    If you can confirm exactly what columns should be present, what data is in which column and which columns the macro should use to name the ranges, and which to populate the data in those named ranges, that will allow me to proceed with confidence.

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

    Re: VBA Transpose

    so what should be present is

    A B C
    Reference - Email Adress - Blank

    "A" Should be the name of the range and B & C Should appear next to A on sheet 2.

    D E
    Item Date

    This will be the information that gets sorted into the named ranges.

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

    Re: VBA Transpose

    If C is blank, there doesn't seem a lot of point in processing it?

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

    Re: VBA Transpose

    Good Point. I think the reason for it was so there was a blank cell in each range to help make it easier when users where looking at the named ranges.

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

    Re: VBA Transpose

    This version will create and populate the named ranges in one go which will hopefully improve the speed. It would be faster still if there were not a few thousand rows on Sheet 1 containing just "_".
    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    will this also sort the other colums into these named ranges

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

    Re: VBA Transpose

    Yes it will.

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

    Re: VBA Transpose

    you are a star thank you and I cant say how much I appreciate your help

    Could you send me the workbook that you have this working on please?

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

    Re: VBA Transpose

    Here is the workbook after running the code. The code is in module 6.
    Attached Files Attached Files

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

    Re: VBA Transpose

    I have tried it and it keeps erroring

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

    Re: VBA Transpose

    What error are you getting and on which line?

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

    Re: VBA Transpose

    can I just confirm what order the Code is meant to run in? sorry Have been in meetings this morning and head is all over the place :S

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

    Re: VBA Transpose

    That file has already had all the code run on it.

    For it to work, you would need to replace all the data on Sheet1 with the original data from the example workbook you uploaded and then delete the blank column C or D after running the first routine.

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

    Re: VBA Transpose

    its coming up with a mismatch error.

    I am running the first session then on to the code you posted and its erroring.

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

    Re: VBA Transpose

    Which line causes the error and did you delete the extra blank column before running the second bit of code?

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

    Re: VBA Transpose

    nope I got it and it works perfectly thank you so much ^_^

    I am just trying to adjust some code now to run through and email off the ranges

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

    Re: VBA Transpose

    You're welcome.

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

    Re: VBA Transpose

    If I wanted to How would I have the code exclude the blank colum?

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

    Re: VBA Transpose

    In the output?

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

    Re: VBA Transpose

    Yeah so it would exclude column C and then still sort E & F into those ranges

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

    Re: VBA Transpose

    I do not have time to test this at the moment but I believe it will do the job

    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    its missing the item codes completley and sorting the references on a 3rd sheet I have set up (ac_no&email)

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

    Re: VBA Transpose

    What data is in which columns on Sheet1 now?

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

    Re: VBA Transpose

    its email account numbers from the Vlookup sheet i have been using to get the emails

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

    Re: VBA Transpose

    I'm afraid that does not answer my question. I need to know the full data layout on Sheet1 when you run the code and what is happening in the output.

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

    Re: VBA Transpose

    here is sheet that I have been running the code on
    Attached Files Attached Files

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

    Re: VBA Transpose

    The previous code I gave you was designed to work with that layout- i.e. it processes columns D and E, rather than E and F as you just requested.

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

    Re: VBA Transpose

    it works perfectly but the email address's arent showing as active. do you know how I could add that into the code.

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

    Re: VBA Transpose

    sorry for all the questions and thank you I really appreciate your help

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

    Re: VBA Transpose

    Do you mean you want them to be active hyperlinks?

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

    Re: VBA Transpose

    Yes please

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

    Re: VBA Transpose

    Replace the code in module 10 with the following
    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    Good Morning Xlnitwit ^_^

    I was just wondering if I could pick your brains quickly on this workbook again please/

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

    Re: VBA Transpose

    I will help if I can.

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

    Re: VBA Transpose

    its just the below code I am using to try an email the ranges on sheet 2

    it keeps giving me a runtime error but I believe I have defined everything correctly

    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    What is the error and on which line? Does the data start in row 1- my memory says it was row 2?

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

    Re: VBA Transpose

    I am trying to use the Sub emailranges to fire off all of the emails on sheet 2 but it keeps on coming up with a runtime error. I was just wondering if you could point me in the right direction so i can get it to work.
    Attached Files Attached Files

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

    Re: VBA Transpose

    I have amended the ranges on sheet2 to row 1 but it is having none of it

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

    Re: VBA Transpose

    You must amend the code thus

    Please Login or Register  to view this content.
    Rather embarrassingly I have just sent several emails from a colleague's machine! It seems that the MailEnvelope does not allow one to step through the code.

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

    Re: VBA Transpose

    LIKE SO ?

    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    Yes, exactly.

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

    Re: VBA Transpose

    its now coming up with a invalid reference on the .introduction

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

    Re: VBA Transpose

    I misread your code- you are missing this
    Please Login or Register  to view this content.
    after the line that makes the envelope visible.

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

    Re: VBA Transpose

    i must have entered it wrong cause it is still showing a runtime error

    Please Login or Register  to view this content.

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

    Re: VBA Transpose

    Quote Originally Posted by bloodmilksky View Post
    i must have entered it wrong cause it is still showing a runtime error
    I have asked this several times before- what is the error and on which line? It is extremely difficult to help without that information.

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

    Re: VBA Transpose

    so it fails on

    "With ActiveSheet.MailEnvelope"

    and the error says

    Run-Time Error"-2147467259(80004005)2:

    Method "mailenvelope"of Object "_Worksheet" Failed

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

    Re: VBA Transpose

    I really can't read today! You haven't made it visible
    Please Login or Register  to view this content.
    Last edited by xlnitwit; 10-07-2016 at 09:27 AM.

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

    Re: VBA Transpose

    Loop without do?

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

    Re: VBA Transpose

    I've edited the code above. And with that I am stopping as I clearly cannot concentrate today!

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

    Re: VBA Transpose

    sorry that message sent early it has come with a Loop without do error. I have tried using a end if to fix it but then it just rolls onto another error.

    I am so sorry for this.....I thought this would be very easy and it has just turned into a utter calamate

+ 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. [SOLVED] Use the TRANSPOSE() function to transpose a range as an input to the UDF
    By alice2011 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-09-2015, 09:35 PM
  2. transpose column to a range calculate and transpose to new table loop
    By moshro1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2015, 10:52 AM
  3. [SOLVED] Excel 2007 : Cut\transpose vs. copy\transpose
    By sarahaley in forum Excel General
    Replies: 3
    Last Post: 05-09-2012, 05:45 PM
  4. [SOLVED] Transpose: How can I copy a list and transpose it but leaving 3 cells in between each item
    By cocolete in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-11-2012, 10:01 AM
  5. Replies: 2
    Last Post: 02-19-2007, 04:53 PM
  6. Replies: 1
    Last Post: 12-29-2005, 07:50 PM
  7. [SOLVED] I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES
    By Umair Aslam in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2005, 09:05 AM

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