LordDragon
08-17-2015, 12:35 PM
Greetings & Salutations,
I am trying to put in some sort options. I have created a user form that has 5 sort options.
Color of the cells.
Part Name
Part Number
Wheather the part was even ordered (Yes/No) (Would like it sorted with Yes on top, so Decending).
Quantity ordered. (Also Decending).
Below is a sample of my first attempt (just sorting by quantity so far). However, I'm getting an error when I test the code.
"Compile Error: Object Required" and it's highlighting the "Set lngRow = Range("A" & Rows.Count).End(xlUp).Row".
Any help getting any one of these sort options to work would be great. I should be able to figure out the rest if I have one that works.
Thanks.
Private Sub btnSort_Click()
'Determines which sort option is selected and calls the correct sub.
'Declare the variables.
Dim nSheets
Dim lngRow As Long
'Dim lngRng As Range
' 'Sort Importance
' If OptionColor Then Call SortImportance
' End If
'
' 'Sort Part Name
' If OptionName Then Call SortPartName
' End If
'
' 'Sort Part Number
' If OptionNumber Then Call SortPartNumber
' End If
'
' 'Sort Ordered (Yes)
' If OptionOrdered Then Call SortPartOrdered
' End If
'Sort Quantity
'Sorts the active sheet by the quantity ordered.
If OptionQuantity Then
'Declare the array of sheets to ignore.
nSheets = Array("Instructions", "Project Info", "Order Summary", "RMS Order", "Master DataList", "Master Parts List")
'Check the active sheet to make sure it is not in the array.
With ActiveWorkbook
If ActiveSheet = nSheets Then Exit Sub
'Determine the number of rows.
With ActiveSheet
Set lngRow = Range("A" & Rows.Count).End(xlUp).Row
'Apply the sort.
With .Sort
.SortFields.Clear
.SortFields.Add Key:=lngRow.Columns("E"), SortOn:=xlSortOnValues, Order:=xlDecending, DataOption:=xlSortNormal
.SetRange lngRow
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End If
End With
End If
End Sub
I am trying to put in some sort options. I have created a user form that has 5 sort options.
Color of the cells.
Part Name
Part Number
Wheather the part was even ordered (Yes/No) (Would like it sorted with Yes on top, so Decending).
Quantity ordered. (Also Decending).
Below is a sample of my first attempt (just sorting by quantity so far). However, I'm getting an error when I test the code.
"Compile Error: Object Required" and it's highlighting the "Set lngRow = Range("A" & Rows.Count).End(xlUp).Row".
Any help getting any one of these sort options to work would be great. I should be able to figure out the rest if I have one that works.
Thanks.
Private Sub btnSort_Click()
'Determines which sort option is selected and calls the correct sub.
'Declare the variables.
Dim nSheets
Dim lngRow As Long
'Dim lngRng As Range
' 'Sort Importance
' If OptionColor Then Call SortImportance
' End If
'
' 'Sort Part Name
' If OptionName Then Call SortPartName
' End If
'
' 'Sort Part Number
' If OptionNumber Then Call SortPartNumber
' End If
'
' 'Sort Ordered (Yes)
' If OptionOrdered Then Call SortPartOrdered
' End If
'Sort Quantity
'Sorts the active sheet by the quantity ordered.
If OptionQuantity Then
'Declare the array of sheets to ignore.
nSheets = Array("Instructions", "Project Info", "Order Summary", "RMS Order", "Master DataList", "Master Parts List")
'Check the active sheet to make sure it is not in the array.
With ActiveWorkbook
If ActiveSheet = nSheets Then Exit Sub
'Determine the number of rows.
With ActiveSheet
Set lngRow = Range("A" & Rows.Count).End(xlUp).Row
'Apply the sort.
With .Sort
.SortFields.Clear
.SortFields.Add Key:=lngRow.Columns("E"), SortOn:=xlSortOnValues, Order:=xlDecending, DataOption:=xlSortNormal
.SetRange lngRow
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End If
End With
End If
End Sub