Dynamic search code that ignores non-numerical data.

[freddie mitchell]

Hello everyone,

A question on a code I'm using to auto sort a table by date, included below. Is it possible to add an IF clause to ignore / skip over non-numerical values from the code? For example, if someone were to change a pre-existing date in the worksheet to "September" the code would not try to auto sort it!

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Range("A4").Sort Key1:=Range("A5"), _
      Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End If

End Sub

Thank you for the help, you're all super

 

[Fluff]

Whilst it's possible to check if text has been entered & stop the code, then next time somebody changes a value in col A the data will be sorted, including the text.

 

[freddie mitchell]

Hi Fluff,

Aah, yes that's a good point. I will look for another solution that does not rely on VBA code.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[freddie mitchell]

Fluff, on a related issue that you might be able to help with.

The previously mentioned auto-sort search code is causing issues for another bit of code, linked below. Could you tell me why this would be?

The issue is that the Insert_Row function is not working. So when the okbutton_click fires it just substitutes existing content in the worksheet, rather than inserting a row.

I know it's the auto-sort causing the issue because when I delete that bit of code then the Insert_Row part works perfectly.

Private Sub OKButton_Click()

'starting insert row function
Call Insert_Row
End Sub

Public Sub Insert_Row()

    Dim r As Variant
    Dim userformDate As Date
    
    userformDate = CDate(Replace(StartDate.Value, ".", "-"))
   
    With Worksheets("CEE rolling calendar of events")
    
        r = Application.Match(CLng(userformDate), .Columns(1), 1)
        If IsError(r) Then
            r = 2
        ElseIf .Cells(r, 1).Value < userformDate Then
            r = r + 1
        End If
    
        .Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
        
        'remove the default text from start date and end date
If StartDate.Value = "DD/MM/YY" Then
StartDate.Value = ""
End If


If EndDate.Value = "DD/MM/YY" Then
EndDate.Value = ""
End If

 

[Fluff]

You could add

If Target.CountLarge > 1 Then Exit Sub

to the very start of your change event. That way it will exit the sub if more than one cell is changed.

 

[freddie mitchell]

That fixed the issue and means that both codes are working beautifully

 

[Fluff]

Glad to help & thanks for the feedback.