Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Melbourne, Victoria, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop down macro (2003 SP 3)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    Hi

    I am looking for assistance to help me write a macro that will cut data from one row and paste to the row below moved one column to the left. The curser then needs to drop-down 1 row and move to the right 1 column and continue the process until the end row is reached.

    Any assistance would be appreciated.

    Thanks

    Norman

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Drop down macro (2003 SP 3)

    I am not entirely clear on what you want to acheive. COuld you post an example before and after and then elaborate a little on what it needs to do?

    Steve

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Location
    Melbourne, Victoria, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down macro (2003 SP 3)

    Steve

    Thanks for your quick response I have attached a small example of what I am trying to do.
    It is my intention to Parse the contents of column A & B but before doing this the top rows (Example B6) need to be moved as per the detail below.

    Cut the contents of cell B6 and paste the contents into A7. Move to cell B8 and cut and paste the contents to A9. Continue this process until all the rows contents in red have been cut and pasted.

    When completed the result will be parsed to enable data selection to proceed.

    Your assistance is appreciated.

    Thanks

    Norman
    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drop down macro (2003 SP 3)

    Hi,
    Something like this should do it (I have assumed that row 7 contains the first bit of data you want moved; if not adjust the initial value of lngIndex as necessary):
    <pre>Sub ArrangeData()
    Dim lngIndex As Long
    lngIndex = 7
    Do Until ActiveSheet.Cells(lngIndex, 2) = ""
    With ActiveSheet.Cells(lngIndex, 2)
    .Offset(1, -1).Formula = .Value
    End With
    lngIndex = lngIndex + 2
    Loop
    End Sub
    </pre>


    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down macro (2003 SP 3)

    Rory's code needs a couple of changed to do exactly what you asked for:

    <code>
    Sub ArrangeData()
    Dim lngIndex As Long
    lngIndex = 7
    Do Until ActiveSheet.Cells(lngIndex, 2) = ""
    With ActiveSheet.Cells(lngIndex, 2)
    .Offset(1, -1).Formula = .Value
    .Offset(1, -1).Font.ColorIndex = .Font.ColorIndex
    .Value = ""
    End With
    lngIndex = lngIndex + 2
    Loop
    End Sub
    </code>
    Legare Coleman

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drop down macro (2003 SP 3)

    Good point! Thanks for catching that. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Oct 2004
    Location
    Melbourne, Victoria, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down macro (2003 SP 3)

    Thanks Guys <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Your code works a treat I will let the guys here know where the solution came from.
    This will save a lot of leg work.

    Thanks

    Norman

Posting Permissions

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