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

[ham fam]

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.

[georgiboy]

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

[ham fam]

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]

[georgiboy]

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

[ham fam]

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?

[georgiboy]

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?

[ham fam]

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?

[ham fam]

What if I arbitrarily created a unique id for each line, then, how would I go about moving the cursor?

[georgiboy]

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