Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Solved
sort on column
Posted on 2016-07-23
I had this question after viewing sort on column.
I am sorting data by a category, then I need to sort the id in sequential order to select an item from the list box. I have completed the sort by column (Sub SortDataByAcctCode()), but keep getting an error message when i sort by the ID (Sub SortID()) I always get an error message starting at ".AutoFilter.sort.SortFiel
Whats happening
I am sorting data by a category, then I need to sort the id in sequential order to select an item from the list box. I have completed the sort by column (Sub SortDataByAcctCode()), but keep getting an error message when i sort by the ID (Sub SortID()) I always get an error message starting at ".AutoFilter.sort.SortFiel
Whats happening
Sub SortDataByAcctCode()
'
' SortDataByAcctCode Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
Active today
Your error is due to the fact that the autofiltering is not turned on when the macro hit this instruction:
.AutoFilter.sort.SortField
It is due to the fact that you are missing this instruction
.Range("A1:Z1").AutoFilter
My suggestion is that your macro should be like this:
gowflow
.AutoFilter.sort.SortField
It is due to the fact that you are missing this instruction
.Range("A1:Z1").AutoFilter
My suggestion is that your macro should be like this:
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.Range("A1:Z1").AutoFilter
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
gowflow
I have three Options Sort by Date Company and Account Code (see codes below) the above (gowflow) works when I select one option, but when i Bounce around from Company to Date to Account Code or any other combination I get an error code highlighting the
.AutoFilter.sort.SortFields.Clear
Sub SortDataByDate()
'
' SortDataByDate Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("I1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortDataByCompany()
'
' SortDataByCompany Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortDataByAcctCode()
'
' SortDataByAcctCode Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.Range("A:A").AutoFilter
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Active today
Simply put this
.Range("A1:Z1").AutoFilter
After this line
ActiveWindow.SmallScroll ToRight:=3
gowflow
.Range("A1:Z1").AutoFilter
After this line
ActiveWindow.SmallScroll ToRight:=3
gowflow
Still not working
I have attached the workbook for your review
Select the Menu Tab
Click on the input Invoice Command Button
the Option Buttons are on the right above the list box
if you want to fix the error message every time i close the workbook, i wouldn't mind
Ops-GL7.4.xlsm
I have attached the workbook for your review
Select the Menu Tab
Click on the input Invoice Command Button
the Option Buttons are on the right above the list box
if you want to fix the error message every time i close the workbook, i wouldn't mind
Ops-GL7.4.xlsm
Featured Post
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses
Course of the Month13 days, 10 hours left to enroll
656 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.