Results 1 to 4 of 4

Thread: Transpose data by modifying an old macro

  1. #1
    New Member
    Join Date
    Dec 2017
    Location
    Central Tablelands NSW, Australia
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Transpose data by modifying an old macro

    Hi guys.
    Need a little help adding some code to a macro, that was an inquiry back in April this year on this forum. This the sample code.


    Sub DoLoop()

    Dim Rng As Range
    Set Rng = Range("E1", Range("E" & Rows.Count).End(xlUp))
    Dim c As Range
    Set c = Rng.Find("MarketPlace", , , xlWhole, , xlNext, True, , False)
    Application.ScreenUpdating = False
    Do While Not c Is Nothing
    'The below line of code, where it is to copy data,This is what I want to modify by adding transpose to the code, I cannot
    'seem to get it to work.

    c.Resize(, 3).Copy c.Offset(5, -3)
    c.Resize(, 3).ClearContents
    Set c = Rng.FindNext(c)
    Loop
    Application.ScreenUpdating = True
    End Sub

    As always any help is greatly appreciated.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Transpose data by modifying an old macro

    Try:
    Code:
    Sub DoLoop()
        Dim Rng As Range
        Set Rng = Range("E1", Range("E" & Rows.Count).End(xlUp))
        Dim c As Range
        Set c = Rng.Find("MarketPlace", , , xlWhole, , xlNext, True, , False)
        Application.ScreenUpdating = False
        Do While Not c Is Nothing
            c.Resize(, 3).Copy
            c.Offset(5, -3).PasteSpecial Transpose:=True
            c.Resize(, 3).ClearContents
            Set c = Rng.FindNext(c)
        Loop
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Dec 2017
    Location
    Central Tablelands NSW, Australia
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transpose data by modifying an old macro

    Reply to Mumps.
    Thank you for your help, it works for me. I know that was simple for you, frustration told me to go here get it out of my head and move on.
    Thanks again Steve.

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Transpose data by modifying an old macro

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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