Hello,
I am needing help from the excel Masters. I am completely lost and have
tried to find my way for the past 3 days. I have given up. Please help me.

If I sort from Column A to column H this macro works great

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("A12"), Order1:=xlAscending, _
Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlSortNormal

Now I need to reverse this whole thing and place the contract #'s in column
H instead of A and the above macro doesn't work. What do I need to do.

Next, I need to sort all of this info by date or by contract #. So, I need
a macro to ask me what contract number to be first so that my program will
graph it.
ie:
123
354
456

Now I want Contract 456 on top.
456
123
354

But now I want Contract 354 on top.

354
123
456

I am sorry to make this so difficult. Hope some on can help me. My email
is

Eric Dickson

I don't think you're going to get excel to sort numbers using your custom sort
criteria. Sorting numbers is gonna be ascending or descending.

Maybe you could add another field that returns a number for each of the
contracts so you could sort by that field.


Eric wrote:

Hello,
I am needing help from the excel Masters. I am completely lost and have
tried to find my way for the past 3 days. I have given up. Please help me.

If I sort from Column A to column H this macro works great

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("A12"), Order1:=xlAscending, _
Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlSortNormal

Now I need to reverse this whole thing and place the contract #'s in column
H instead of A and the above macro doesn't work. What do I need to do.

Next, I need to sort all of this info by date or by contract #. So, I need
a macro to ask me what contract number to be first so that my program will
graph it.
ie:
123
354
456

Now I want Contract 456 on top.
456
123
354

But now I want Contract 354 on top.

354
123
456

I am sorry to make this so difficult. Hope some on can help me. My email
is

Eric Dickson

--

Dave Peterson

Dave,

What if I use a list box and ask for a certain contract #. Also, What about
the first part of my problem?

Eric

"Dave Peterson" wrote:

I don't think you're going to get excel to sort numbers using your custom sort
criteria. Sorting numbers is gonna be ascending or descending.

Maybe you could add another field that returns a number for each of the
contracts so you could sort by that field.


Eric wrote:

Hello,
I am needing help from the excel Masters. I am completely lost and have
tried to find my way for the past 3 days. I have given up. Please help me.

If I sort from Column A to column H this macro works great

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("A12"), Order1:=xlAscending, _
Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlSortNormal

Now I need to reverse this whole thing and place the contract #'s in column
H instead of A and the above macro doesn't work. What do I need to do.

Next, I need to sort all of this info by date or by contract #. So, I need
a macro to ask me what contract number to be first so that my program will
graph it.
ie:
123
354
456

Now I want Contract 456 on top.
456
123
354

But now I want Contract 354 on top.

354
123
456

I am sorry to make this so difficult. Hope some on can help me. My email
is

Eric Dickson

--

Dave Peterson

I don't think you're ever going to be able to sort numbers into the order you
want using plain old excel's dialogs. Maybe a helper column. Maybe changing
the data.

Maybe something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim RngToSort As Range
Dim LastRow As Long
Dim StartCell As Range

Set wks = ActiveSheet
With wks
Set StartCell = .Range("H11")
LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row

Set RngToSort = .Range("A11:H" & LastRow)

With RngToSort
.Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End With

End Sub

I figure you know if the data has headers. Change xlyes to xlno if there are no
headers--but why take a chance that excel will guess incorrectly.

I used column H to find the last used row--is that ok?

And I don't how many columns are in the range to be sorted. I use A:H.

And do you still want to sort by the second column in the range (as the
secondary key)?






Eric wrote:

Dave,

What if I use a list box and ask for a certain contract #. Also, What about
the first part of my problem?

Eric

"Dave Peterson" wrote:

I don't think you're going to get excel to sort numbers using your custom sort
criteria. Sorting numbers is gonna be ascending or descending.

Maybe you could add another field that returns a number for each of the
contracts so you could sort by that field.


Eric wrote:

Hello,
I am needing help from the excel Masters. I am completely lost and have
tried to find my way for the past 3 days. I have given up. Please help me.

If I sort from Column A to column H this macro works great

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("A12"), Order1:=xlAscending, _
Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlSortNormal

Now I need to reverse this whole thing and place the contract #'s in column
H instead of A and the above macro doesn't work. What do I need to do.

Next, I need to sort all of this info by date or by contract #. So, I need
a macro to ask me what contract number to be first so that my program will
graph it.
ie:
123
354
456

Now I want Contract 456 on top.
456
123
354

But now I want Contract 354 on top.

354
123
456

I am sorry to make this so difficult. Hope some on can help me. My email
is

Eric Dickson

--

Dave Peterson


--

Dave Peterson

Dave,

Thank you so much for your help I will be trying this on Friday morning.
You asked if the second column was going to be the second key and the answer
is yes.

In sorting the data by contract number you said that I may need to use a
helper column, what exactly do you mean? What kind of information would be
in that helper column. It can't be a number correct, because if it were we
would be back to the same problem as before sorting in ascending or decending
order. So what should that column be? Each contract will have a slight
variasion of each other so how will know what to put in that helper column or
will I have to go in an manually enter something?
IE:
Contract # Helper Column (town)
456 manchester
234 Windsor
123 Mystic

Would I know sort according to Town?
If I do this will each time I enter 456 will Manchester automaticlly be
put in to the Town column?


ERic


"Dave Peterson" wrote:

I don't think you're ever going to be able to sort numbers into the order you
want using plain old excel's dialogs. Maybe a helper column. Maybe changing
the data.

Maybe something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim RngToSort As Range
Dim LastRow As Long
Dim StartCell As Range

Set wks = ActiveSheet
With wks
Set StartCell = .Range("H11")
LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row

Set RngToSort = .Range("A11:H" & LastRow)

With RngToSort
.Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End With

End Sub

I figure you know if the data has headers. Change xlyes to xlno if there are no
headers--but why take a chance that excel will guess incorrectly.

I used column H to find the last used row--is that ok?

And I don't how many columns are in the range to be sorted. I use A:H.

And do you still want to sort by the second column in the range (as the
secondary key)?






Eric wrote:

Dave,

What if I use a list box and ask for a certain contract #. Also, What about
the first part of my problem?

Eric

"Dave Peterson" wrote:

I don't think you're going to get excel to sort numbers using your custom sort
criteria. Sorting numbers is gonna be ascending or descending.

Maybe you could add another field that returns a number for each of the
contracts so you could sort by that field.


Eric wrote:

Hello,
I am needing help from the excel Masters. I am completely lost and have
tried to find my way for the past 3 days. I have given up. Please help me.

If I sort from Column A to column H this macro works great

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("A12"), Order1:=xlAscending, _
Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlSortNormal

Now I need to reverse this whole thing and place the contract #'s in column
H instead of A and the above macro doesn't work. What do I need to do.

Next, I need to sort all of this info by date or by contract #. So, I need
a macro to ask me what contract number to be first so that my program will
graph it.
ie:
123
354
456

Now I want Contract 456 on top.
456
123
354

But now I want Contract 354 on top.

354
123
456

I am sorry to make this so difficult. Hope some on can help me. My email
is

Eric Dickson

--

Dave Peterson


--

Dave Peterson

First, when I use the word Sort, I want an alpha-numeric sort. I don't
understand why you'd want to sort a field to put a certain value on the top and
then sort again to put another value at the top of the sorted list.

You could define a custom list (tools|Options|custom lists) and excel will sort
your data according to that list.

But if you want to rearrange values, you'll have to delete that list and
recreate a different custom list.

My thought was that you could create a table on another sheet (say sheet2) and
build a table that defines how you want your data sorted.

Contract Sortorder1 Sortorder2 sortorder3 sortorder4
1234 4 1 1 4
1235 1 2 3 3
1236 2 4 4 1
1237 3 3 2 2

Then you could use a helper column and put a formula like:
=vlookup(a2,sheet2!a:e,2,false)
and copy down.
Then sort your data using that column.

If you needed to sort in a different order, you could change the =vlookup()
formula to return a different column.

Or you could use lots of helper columns and return every conceivable order you
want and then sort your data by whatever column you want.

But I'm still confused.

Eric wrote:

Dave,

Thank you so much for your help I will be trying this on Friday morning.
You asked if the second column was going to be the second key and the answer
is yes.

In sorting the data by contract number you said that I may need to use a
helper column, what exactly do you mean? What kind of information would be
in that helper column. It can't be a number correct, because if it were we
would be back to the same problem as before sorting in ascending or decending
order. So what should that column be? Each contract will have a slight
variasion of each other so how will know what to put in that helper column or
will I have to go in an manually enter something?
IE:
Contract # Helper Column (town)
456 manchester
234 Windsor
123 Mystic

Would I know sort according to Town?
If I do this will each time I enter 456 will Manchester automaticlly be
put in to the Town column?

ERic

"Dave Peterson" wrote:

I don't think you're ever going to be able to sort numbers into the order you
want using plain old excel's dialogs. Maybe a helper column. Maybe changing
the data.

Maybe something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim RngToSort As Range
Dim LastRow As Long
Dim StartCell As Range

Set wks = ActiveSheet
With wks
Set StartCell = .Range("H11")
LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row

Set RngToSort = .Range("A11:H" & LastRow)

With RngToSort
.Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End With

End Sub

I figure you know if the data has headers. Change xlyes to xlno if there are no
headers--but why take a chance that excel will guess incorrectly.

I used column H to find the last used row--is that ok?

And I don't how many columns are in the range to be sorted. I use A:H.

And do you still want to sort by the second column in the range (as the
secondary key)?






Eric wrote:

Dave,

What if I use a list box and ask for a certain contract #. Also, What about
the first part of my problem?

Eric

"Dave Peterson" wrote:

I don't think you're going to get excel to sort numbers using your custom sort
criteria. Sorting numbers is gonna be ascending or descending.

Maybe you could add another field that returns a number for each of the
contracts so you could sort by that field.


Eric wrote:

Hello,
I am needing help from the excel Masters. I am completely lost and have
tried to find my way for the past 3 days. I have given up. Please help me.

If I sort from Column A to column H this macro works great

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("A12"), Order1:=xlAscending, _
Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlSortNormal

Now I need to reverse this whole thing and place the contract #'s in column
H instead of A and the above macro doesn't work. What do I need to do.

Next, I need to sort all of this info by date or by contract #. So, I need
a macro to ask me what contract number to be first so that my program will
graph it.
ie:
123
354
456

Now I want Contract 456 on top.
456
123
354

But now I want Contract 354 on top.

354
123
456

I am sorry to make this so difficult. Hope some on can help me. My email
is

Eric Dickson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson