Consulting

Results 1 to 9 of 9

Thread: Need help updating sort automatically, but only if a cell has a number

  1. #1
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location

    Need help updating sort automatically, but only if a cell has a number

    I am trying to automatically sort by date in my excel document. But only update if there is a number (0 or 1) in the Q column of the row I am making a change in.

    Here is what I've got so far.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Range(ActiveCell, "Q2:Q1000")) Is Nothing Then
      
    Range("g1").Sort Key1:=Range("g2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
             
     End If
        
    End Sub
    When I run it I get Argument not optional. What I am expecting is, if there is nothing in the Q column of the row I am updating the date in (which is g column), then do nothing, but if there is a number (0 or 1), then resort the document.

  2. #2
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    Hi Ham,

    Maybe something like:

    Private Sub Worksheet_Change(ByVal Target As Range)    
        If Not Intersect(Target, Range("G2:G1000")) Is Nothing Then
            If Range("Q" & Target.Row) = 0 Or Range("Q" & Target.Row) = 1 Then
                Range("G1:G1000").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            End If
        End If
    End Sub
    Hope this helps
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  3. #3
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    Thanks for the help. I did make one change in red text below. Your version seemed to only update the G column and not all of the surrounding columns. I believe my change instead seems to sort all columns. But now it seems to sort regardless of whether there is a number in the corresponding q column?

    Private Sub Worksheet_Change(ByVal Target As Range)    
        If Not Intersect(Target, Range("G2:G1000")) Is Nothing Then
            If Range("Q" & Target.Row) = 0 Or Range("Q" & Target.Row) = 1 Then
                Range("G1:G1000").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            End If
        End If
    End Sub
    [/QUOTE]

  4. #4
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    Maybe instead of:
    If Range("Q" & Target.Row) = 0 Or Range("Q" & Target.Row) = 1 Then
    Try
    If Range("Q" & Target.Row) = "0" Or Range("Q" & Target.Row) = "1" Then
    Otherwise maybe post your amended code.

    Hope this helps
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  5. #5
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    Thanks, that seems to work.

    Follow up question, is there a way that after the sort I move the "active cell" to the cell that I just changed? So, for example, I change cell g14, and it auto sorts that row up to row 5, can I have the cursor, or "active cell" move up there too?

  6. #6
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    To do that we would need something to hold as a value to then later find, In the examples so far we do not have an identifier like UPC number or collegue ID.

    Is there an identifier of some kind in another column?
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  7. #7
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    Not there aren't any.

    Next question i seemed to have saved a recorded macro for Ctrl+Shift+S, apparently when i click that combination i wind up with a horizontal and vertacal red lines. I've tried re-recording using the same buttons, but it doesn't seem to take. Any idea what the horizontal and vertical lines are, or how to reset a recorded macro?

  8. #8
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    What if I arbitrarily created a unique id for each line, then, how would I go about moving the cursor?

  9. #9
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    Looking back at previous examples the below should give you an idea, this is assuming the ID was in column F:

    Private Sub Worksheet_Change(ByVal Target As Range)    
        Dim tmpStr As String
        
        If Not Intersect(Target, Range("G2:G1000")) Is Nothing Then
            If Range("Q" & Target.Row) = "0" Or Range("Q" & Target.Row) = "1" Then
                tmpStr = Range("F" & Target.Row).Value
                Range("F1:G1000").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
                Range("F:F").Find(tmpStr, , , xlWhole).Offset(, 1).Select
                tmpStr = vbNullString
            End If
        End If
    End Sub
    Hope this helps
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

Posting Permissions

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