Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Move a range of cells up or down of another to change sequence of production items

    Hi all,
    As a part of the tool i have been working on to guide operators in a production process, i need to add an attribute to a table which we can move a range of cells up or down of another to change the sequence of production items.

    In the 'Schedule' sheet of the attached file,

    A2:N41 is the range i create production lines.

    An example operation that i need should happen like follows;

    The line with ID=142 should move up and replace the line with ID=192, or the same while moving down to replace ID=143 and the replaced array should go into this array's position.

    I'm open to any efficient solutions and would appreciate guidance. I'm adding my excel file to this message.
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I've been thinking on the 'replacement operation' for a time now. The best idea i had so far is, somehow i can store the range i select and my target replacement range into another location in the book -though still no idea how to select the range i want- and sort them in reverse order, and finally get the 2 records back to my original table as we do 'insert'.
    There will be complications:
    First; my original table should have a set of formatting as it should provide visual management elements -that's the idea at all-
    Second; how on earth can i pick my target array when i get the array i want to move? there will be 2 different choices every time; up or down

  3. #3
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts
    at the beginning i presumed that the ID i give to each record would not be consecutive at a stage users operate on the tool. would it? well, no now because my table is all vulnarable to manual operations right now. also, i will need to carry my ID's with my arrows so, i can't use integers at ID column for a sorting.
    I'm kinda stuck at this stage. Hoping my guesswork above would make a bit of sense to you guys.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    lerq,

    I have to say you have me totally confused.

    When you say replace do you really mean DELETE the replaced row (e.g. 142 moves up after 192 is "replaced")? Or are you saying you want 2 copies of the e.g. 142 replaces 192 and there are now two 142's?

    Before and After example worksheets would be most useful.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    lerq,

    Here is some code that will move your lines up or down swapping with the row it displaces. Click in an ID# in column A. A spinner will appear next to the ID#. Click up or down to move the row and the spinner will follow the row. The rows will not move above row 2 nor below row 41. The spinner will disappear if you are working on any other part of the sheet.

    HTH,
    Maud

    Parking.png

    Code:
    Private Sub SpinButton1_SpinDown()
    Application.ScreenUpdating = False
        Dim ID(14), line As Integer
        line = ActiveCell.row
        If line = 41 Then Exit Sub
        For I = 1 To 14
            ID(I) = Cells(line, I)
        Next I
        For I = 1 To 14
            Cells(line, I) = Cells(line, I).Offset(1, 0)
        Next I
        For I = 1 To 14
            Cells(line, I).Offset(1, 0) = ID(I)
        Next I
        Cells(line, I).Select
        Cells(line, 1).Offset(1, 0).Select
    Application.ScreenUpdating = True
    End Sub
    
    Private Sub SpinButton1_SpinUp()
    Application.ScreenUpdating = False
        Dim ID(14), line As Integer
        line = ActiveCell.row
        If line = 2 Then Exit Sub
        For I = 1 To 14
            ID(I) = Cells(line, I)
        Next I
        For I = 1 To 14
            Cells(line, I) = Cells(line, I).Offset(-1, 0)
        Next I
        For I = 1 To 14
            Cells(line, I).Offset(-1, 0) = ID(I)
        Next I
        Cells(line, I).Select
        Cells(line, 1).Offset(-1, 0).Select
    Application.ScreenUpdating = True
    End Sub
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
     If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A1:A41")) Is Nothing Then
            SpinButton1.Visible = True
            SpinButton1.Top = Target.Top - 1.5
        Else:
            SpinButton1.Visible = False
        End If
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-05-19 at 07:51. Reason: Spelling errors

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    lerq (2015-05-19)

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    I missed that option completely!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    lerq (2015-05-19)

  9. #7
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Maud.
    I imported the code and the spin button and it works perfect.
    The best side of your method is that formattings stay at the same cells which will help us to be able to visually manage the process on the shopfloor.
    Currently our people spend a lot of time figuring out what job they have to pick next and it's a disaster when the SV want to change the production Parts. This will improve our productivity, dramatically!

Posting Permissions

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