Reorder List VBA

[Jlragr]

Hi all,

I’m working on a small task for work where we have a list of technicians who do a task. We are asked in order, to do that task but if someone says yes, they move to the bottom of the list and if someone says no, they stay where they are in the list.

For example, the original list starting from the top or who is first to be asked is as follows:

Jenn
Jessica
Rhiannon
Briana
Andrew

Let’s say Jenn was asked to do a task but was unable to, so her response was no, she would stay in her spot within the list. Jessica would be asked next and if she says yes, would move to the bottom of the list and everyone after her would move up. So in this case the list would now look as follows:

Jenn
Rhiannon
Briana
Andrew
Jessica

Is there a formula or a way to code this?
As technicians are asked, our manager would log it into the sheet with our name and our response.

I would like for the VBA or formula to get the techs name and response, and if their response is yes, automatically update the order of the list accordingly. It would be even better if i could incorporate a button to click after the information has been entered to do this instead of pulling up the macro window every time, as our manager is not tech savvy whatsoever.

An example picture with my set up is attached.

Thanks in advance and let me know if there’s any more information you’d need to make this easier to understand.

 

[Artik]

Insert the following event procedure into the sheet module where you want this to work.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long
    Dim v As Variant
    Dim vTmp As Variant
    Dim Tmp As Variant
    Dim i As Long
    Dim lPos As Long

    If Target.CountLarge > 1 Then Exit Sub
    If Target.Row < 2 Then Exit Sub

    If Not Intersect(Target, Me.Columns("D")) Is Nothing Then
        If Len(Target.Value) = 0 Then Exit Sub

        If LCase(Target.Value) = "yes" Then
            lRow = Me.Cells(Me.Rows.Count, "G").End(xlUp).Row
            v = Me.Range("G2:G" & lRow).Value
            lPos = Application.Match(Target.Offset(, -1).Value, v, 0)
            
            vTmp = v
            Tmp = v(lPos, 1)

            For i = lPos To UBound(v) - 1
                v(i, 1) = vTmp(i + 1, 1)
            Next i
            
            v(i, 1) = Tmp
            
            Me.Range("G2:G" & lRow).Value = v
        End If
    End If
    
End Sub

Artik

 

[Peter_SSs]

Let’s say Jenn was asked to do a task but was unable to, so her response was no, she would stay in her spot within the list.

An example picture with my set up is attached.

In the picture (XL2BB would be better so we could copy the data to test with) it seems Jenn answered 'No' yet she appears to have moved from top to bottom of the list. Have I misinterpreted the picture or the requirement?

 

[Peter_SSs]

Duplicate to: Reorder list with formula or VBA

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.