Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Backwards
If I sort from Column H to column A by putting a negative infront of the
letter it isn't working. Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("h11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, -6)<~~~~This is where I am talking about. If I go from A to H everything is fine. myrange.sort key1:=Range("h12"), Order1:=xlAscending, _ Key2:=Range("g12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Also, how do I get this to sort by Placing a specific contract number on top. ie: 123 354 456 Now I want it to look like this 456 123 354 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Backwards
Eric,
I am not sure what you are trying to do, but you cannot have a negative size for a range object. Set myrange = myrange.Resize(lrow - myrange.Row + 1, -6) But you can have a negative offset from a range object.... Perhaps: Set myrange = myrange.Offset(0,-6).Resize(lrow - myrange.Row + 1,1) For your second question, you can sort in a specific order if you first create a list in the order that you desire, then use Tools / Options... Custom List to create a list. When you sort, you need to pick that list from the "Sort Order" box. Record a macro when you do it to get the syntax and list number. HTH, Bernie MS Excel MVP "Eric" wrote in message ... If I sort from Column H to column A by putting a negative infront of the letter it isn't working. Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("h11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, -6)<~~~~This is where I am talking about. If I go from A to H everything is fine. myrange.sort key1:=Range("h12"), Order1:=xlAscending, _ Key2:=Range("g12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Also, how do I get this to sort by Placing a specific contract number on top. ie: 123 354 456 Now I want it to look like this 456 123 354 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Backwards
Bernie,
What you told me to do didn't work. But maybe I can explain it better this time. Ex: Gmm Gse date Contr# 2.567 2.987 9/12 123 2.777 2.991 9/13 345 2.568 2.988 9/14 123 What I want to do is be able to sort this information and graph it. The way it is in the Ex. I can graph all properties in the order that it appears. with the last test being posted on the last line. Now I want to graph by contract placing (whichever Contract # I want) First in order to graph. I want it to look like this EX: or like this Gmm Gse date Contr# 2.777 2.991 9/13 345 2.567 2.987 9/12 123 2.568 2.988 9/14 123 Using the following macro I can make it work if the Contr # and Date are on the Left of the sheet but not on the right. The other issue is when I use this macro the contract #'s list in conilogical order not by whichever one I want first. Sub sortbydate() ' **********This is to sort the test data base according to Date when date is on Left not right************** Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("A11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6) myrange.sort key1:=Range("B12"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal End Sub I hope this helps out....... "Bernie Deitrick" wrote: Eric, I am not sure what you are trying to do, but you cannot have a negative size for a range object. Set myrange = myrange.Resize(lrow - myrange.Row + 1, -6) But you can have a negative offset from a range object.... Perhaps: Set myrange = myrange.Offset(0,-6).Resize(lrow - myrange.Row + 1,1) For your second question, you can sort in a specific order if you first create a list in the order that you desire, then use Tools / Options... Custom List to create a list. When you sort, you need to pick that list from the "Sort Order" box. Record a macro when you do it to get the syntax and list number. HTH, Bernie MS Excel MVP "Eric" wrote in message ... If I sort from Column H to column A by putting a negative infront of the letter it isn't working. Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("h11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, -6)<~~~~This is where I am talking about. If I go from A to H everything is fine. myrange.sort key1:=Range("h12"), Order1:=xlAscending, _ Key2:=Range("g12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Also, how do I get this to sort by Placing a specific contract number on top. ie: 123 354 456 Now I want it to look like this 456 123 354 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index backwards | Excel Worksheet Functions | |||
Excel is Backwards! | Excel Discussion (Misc queries) | |||
Vlookup backwards | Excel Discussion (Misc queries) | |||
backwards sum | Excel Programming | |||
Tab backwards | Excel Programming |