macro for arranging by Contract #

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 21 Sep 2007 16:00 GMT

Hello experts,

I am trying to arrange information by contract # or by date. I produce a
material everyday and input the information into the spreadsheet the latest
is the last line but the contract #'s vary per day (See example #1). I want
to organize everything either by contract # or by date so that I can graph
each accordingly (example #2). The graphing part is working either way I
put the data in to the sheet.

Example #1:
Date Cont # Gmm Gse #200
7/18 123 2.654 2.890 5.2
7/20 564 2.650 2.889 4.2
7/21 123 2.657 2.895 6.8

Example #2:
7/18 123 2.654 2.890 5.2
7/21 123 2.657 2.895 6.8
7/20 564 2.650 2.889 4.2

Any help would be appreciated. Thank you all in advance!!

Eric

Reply to this Message

Barb Reinhardt - 21 Sep 2007 16:26 GMT

Try something like this. This assumes that your headers are in row 1.

Sub Sort()

Dim aWS As Worksheet
Dim myRange As Range
Dim lRow As Long
Set aWS = ActiveSheet
Set myRange = aWS.Range("A1")
lRow = aWS.Cells(aWS.Rows.Count, myRange.Column).End(xlUp).Row

Set myRange = myRange.Resize(lRow - myRange.Row + 1, 5) '<~~~can change the
# of columns here
myRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

Signature

HTH,
Barb Reinhardt

> Hello experts,
>
[quoted text clipped - 19 lines]
>
> Eric

Reply to this Message

Eric - 21 Sep 2007 18:12 GMT

The headers are not in row 1 they are in row 26. I am assuming that this
would change the macro at this point; Set myRange = aWS.Range("A1") change
("A1") to ("A26"). Correct?

Eric

> Try something like this. This assumes that your headers are in row 1.
>
[quoted text clipped - 38 lines]
> >
> > Eric

Reply to this Message

Eric - 21 Sep 2007 20:38 GMT

Barb,

This command works great with 2 exceptions.

1. What if I want to have a certain contract that I want on top
ie:456 instead of 123. With the macro you gave me it does the contracts in
numerical order.

2. If I have to count the columns backwards how do I do this? Putting in a
negative sign isn't working nor is placing brackets around it.

IE:
Set myRange = aWS.Range("H1")
lRow = aWS.Cells(aWS.Rows.Count, myRange.Column).End(xlUp).Row

Set myRange = myRange.Resize(lRow - myRange.Row + 1,- 5)

I again appreciate all the help. Have a great weekend.

Eric

> Try something like this. This assumes that your headers are in row 1.
>
[quoted text clipped - 38 lines]
> >
> > Eric

Reply to this Message