Auto sorting

[Guest]

Dear all,

I've a table of records. Each record is filled in the spreadsheet by a macro
automatically. Column B is for the "Department's name" and column E is for
the "input date". When each record is filled along the rows, the department
name is not in order. Please advise how to sort the department name in
ascending order automatically and subject to the input date (records with
earlier date come first) whenever a new record is entered?

Thanks in advance

 

[Andy Brown]

Dear all,

I've a table of records. Each record is filled in the spreadsheet by a
macro
automatically. Column B is for the "Department's name" and column E is for
the "input date". When each record is filled along the rows, the
department
name is not in order. Please advise how to sort the department name in
ascending order automatically and subject to the input date (records with
earlier date come first) whenever a new record is entered?

If record "writing" is handled by a macro, you just need a little extra code
at the end of the existing to handle the autosort.

Assuming you have labels in A1:E1, try:

Range("A1").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlAscending,
Key2:=Range("E2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom

 

[Guest]

Hi Andy,

My macro for record writing is as follows:

Private Sub OKButton_Click()
Sheets("Sheet2").Activate

Dim NextRow As Long
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
If TextName.Text = "" Then
MsgBox "You must enter a name."
Exit Sub
End If
If TextDept.Text = "" Then
MsgBox "A Department name must be entered."
Exit Sub
End If
Cells(NextRow, 1) = TextName.Text
Cells(NextRow, 2) = TextDept.Text

If OptionJoin Then Cells(NextRow, 3) = "Yes"
If OptionNotJoin Then Cells(NextRow, 4) = "No"

TextName.Text = ""
TextDept.Text = ""
OptionNotJoin = True
TextName.SetFocus
MsgBox "You're done! Thank you."
End Sub

The userform will appear in Sheet1 for user input and the reocrds will be
filled into rows in Sheet2 automatically. When I put your code at the end of
the above code, a compile error message "Expected: named parameter" appeared.
Please advise anything wrong with it and where should I put the code?

Thanks in advance.