Home

Need Help with Sorting (Macro)

Highlighted
jim_vbanoob
New Contributor

Hi All,

 

I'm trying to run the Macro below but it crashes every time it get to the highlighted .Apply line. I have no idea why since it did not crash on the first .Apply line. Can someone please point me to the right direction?

 

Thanks

 

Dim arng As Range
Dim brng As Range
Dim crng As Range

Set arng = wsSAdj.Range("A:A")
Set brng = wsSAdj.Range("J:J")
Set crng = wsSAdj.Range("S:S")

For Each icell In brng.Cells
If Right(icell.Value, 5) = "Total" Then
Worksheets("Slides - ADJ").Sort.SortFields.Add Key:=Range( _
icell.Offset(-1, 3), icell.Offset(-1, 3).End(xlUp)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Slides - ADJ").Sort
.SetRange Range(icell.Offset(-1, 1), icell.Offset(-1, 1).End(xlToRight).End(xlUp))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
Next


For Each icell In crng.Cells
If Right(icell.Value, 5) = "Total" Then
Worksheets("Slides - ADJ").Sort.SortFields.Add Key:=Range( _
icell.Offset(-1, 3), icell.Offset(-1, 3).End(xlUp)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Slides - ADJ").Sort
.SetRange Range(icell.Offset(-1, 1), icell.Offset(-1, 1).End(xlToRight).End(xlUp))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
Next

Related Conversations
Formula or function for IF statement based on cell color
Laurie McDowell in Excel on
14 Replies
Copy Data to Other Sheets' Columns Based on Criteria
Rob Nunley in Excel on
8 Replies
Compile error: Method or data member not found
kevin whiteside in Excel on
5 Replies
sum by color when colors are set by conditional formatting
matt nipper in Excel on
197 Replies
Re: Macro to save as PDF with auto filename as cell value
Joe Gray in Excel on
23 Replies
Graph doesn't update automatically
Frank Hille in Excel on
3 Replies