Sort Macro: Help with code.

[Guest]

I have a macro which is to sort the names of two worksheets. Worksheet
"Policy Info" sorts correctly however worksheet "Corn Yields" (sort colunn A
which are names, test data range is A6: P12) does not. Columns C thru P
(data fields) are not sorted. Column A is used for row count and cell A13
yields a FALSE when =ISNUMBER is used to check the contents. My code
follows. I would be most grateful to someone who could debug it as I am,
frankly, out of my league here.

Thanks in advance....


Sheets("Policy Info").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:O" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Corn Yields").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Policy Info").Select
End Sub

 

[Guest]

I'd tweak it a bit to include

Dim myWS as worksheet
Dim SortRange as range

Set myWS = nothing
on error resume next
Set myWS = Sheets("Policy Info")
On error goto 0
if not myWS is nothing then

LastRow = myWS.Cells(myws.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6:O" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end if

Do the same for the other sort. I'm guessing it's sorting on the wrong
sheet.

HTH,
Barb Reinhardt

 

[Don Guillett]

may need to
SortRange.Sort Key1:=myws.Range("A6"), _

 

[Guest]

Thank you Barb.

I cannot work on this again until tonight or tomorrow am. Please stand by
while I try to figure it out on my own. However, I may need to post here
again asking for more help!

Thanks again.

 

[Guest]

My code now error out on the last line. When I use just End If, the error
message is Expected End Sub. When I add End Sub after the End If, I get
Compile error: Block If without End If. Stumped again. Any help is greatly
appreciated! Code as currently written follows:


Dim myWS As Worksheet
Dim SortRange As Range

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets("Policy Info")
On Error GoTo 0
If Not myWS Is Nothing Then

LastRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6:O" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets("Corn Yields")
On Error GoTo 0
If Not myWS Is Nothing Then

LastRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End Sub

 

[Dave Peterson]

Without looking very closely.

Make sure you qualify the ranges that are used for the keys:

SortRange.Sort Key1:=myWS.Range("A6"), _

Depending on where the code is, the unqualified range (range("A6")) will either
refer to the activesheet (in a General module) or the sheet that owns the code
(in a worksheet module).

 

[Guest]

Thank you Dave for your reply.

The macro associated with all my grief appears to be "General" (upper left
corner drop down box in VBA mode). I really do not know the difference as
you attempted to explain it.

What should the final two lines read? I know hardly anything about VBA.

Your help, as is all of the other's, is most appreciated.

 

[Guest]

I looked closer at the VBA screen. The code is being stored with the
workbook containing the worksheets to be sorted. It appears to be being
stored, for reasons totally unknown to me, in Module 3.

HTH

 

[Dave Peterson]

Module3 (no space) is most likely a General module.

My suggestion was to change two lines that looked like:
SortRange.Sort Key1:=Range("A6"), _

by adding "myWs." so that you end up with:
SortRange.Sort Key1:=myWS.Range("A6"), _

 

[Guest]

Thank you Dave.

I changed the two lines as per you suggestion.

The code still errors out at the end. What should the final lines read? It
doesn't like:
End If

nor does it like:
End Sub

nor does it like:
End If
End Sub

I have no idea. Suggestions?

Thanks again.
End Sub

 

[Guest]

This Sort was created by the macro editor in seconds. The code should be
altered to include the ranges you would like to have sorted. I sorted my
ranges 1 column at a time but one after another, Be sure to click contunue
with current selection each time you sort a column. It appears to have a
glitch when sorting mult columns (Ranges) at one time.
Part of the problem may be with the clear function that doesn't exist in
your code.
Creating a MacroFilter only takes moments, so I would record the macro over
and then do testing until your satified with the results.
This is the VBA code that the computer generated for comparison

Columns("D").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("D1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("D16")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:E").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("E1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("E1:E6")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
--
William<"M"

Thank you Dave.

I changed the two lines as per you suggestion.

The code still errors out at the end. What should the final lines read? It
doesn't like:
End If

nor does it like:
End Sub

nor does it like:
End If
End Sub

I have no idea. Suggestions?

Thanks again.
End Sub

 

[Dave Peterson]

You had another error, but it wasn't at the bottom. Excel's VBA was looking for
an "End if" in the top portion of your code and didn't find it by the time the
code ended with that "end sub" line. So it yelled near the bottom.

This worked for me:

Option Explicit
Sub testme()

Dim myWS As Worksheet
Dim SortRange As Range
Dim LastRow As Long '<-- Added

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets("Policy Info")
On Error GoTo 0
If Not myWS Is Nothing Then

LastRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6:O" & LastRow)
SortRange.Sort Key1:=myWS.Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If '<-- Added

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets("Corn Yields")
On Error GoTo 0
If Not myWS Is Nothing Then
LastRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6" & LastRow)
SortRange.Sort Key1:=myWS.Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If

End Sub

Thank you Dave.

I changed the two lines as per you suggestion.

The code still errors out at the end. What should the final lines read? It
doesn't like:
End If

nor does it like:
End Sub

nor does it like:
End If
End Sub

I have no idea. Suggestions?

Thanks again.
End Sub

 

[Guest]

Thank you all, especially Dave who stayed with me until the final bell.

All is well now.

All responders to us "know just enough to be dangerous" users find you to be
a true asset to this high tech community.

 

[Dave Peterson]

This community is pretty good. One person may be the first to chime in, but
lots of others will add value/correct mistakes or just offer different ways of
doing things.

It's pretty nice that way.

Thank you all, especially Dave who stayed with me until the final bell.

All is well now.

All responders to us "know just enough to be dangerous" users find you to be
a true asset to this high tech community.