Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Policy Info").Select End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
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 "Tail Wind" wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Policy Info").Select End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
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. "Barb Reinhardt" wrote: 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 "Tail Wind" wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Policy Info").Select End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub "Tail Wind" wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Policy Info").Select End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
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). Tail Wind wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub "Tail Wind" wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Policy Info").Select End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
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. "Dave Peterson" wrote: 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). Tail Wind wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub "Tail Wind" wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Policy Info").Select End Sub -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
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 "Tail Wind" wrote: 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. "Dave Peterson" wrote: 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). Tail Wind wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub "Tail Wind" wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Policy Info").Select End Sub -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Macro: Help with code.
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"), _ Tail Wind wrote: 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 "Tail Wind" wrote: 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. "Dave Peterson" wrote: 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). Tail Wind wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub "Tail Wind" wrote: 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:P" & LastRow) SortRange.Sort Key1:=Range("A6"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Policy Info").Select End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Deleting code from a macro (by a macro) | Excel Discussion (Misc queries) | |||
macro code to sort a range | Excel Discussion (Misc queries) | |||
Sort and print .pdf files into one .pdf document via VBA code? | Excel Discussion (Misc queries) | |||
Zip code sort is an Excel problem even when using the special cat. | Excel Discussion (Misc queries) |