Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort routine in a macro (XP)

    Hello,

    I wrote the macro below and it works beautifully (you must place your cell pointer in cell B3 before you run it). I now need to insert a sort routine in the middle of it so each column will be sorted Z-A before a new column is inserted. See below:

    Do Until ActiveCell = 0
    If ActiveCell.Offset(0, 1) > 0 Then
    ActiveCell.Offset(0, 1).Select

    I need to sort by this column.

    Selection.EntireColumn.Insert
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.FormulaR1C1 = "Rank"
    With ActiveCell.Characters(Start:=1, Length:=6).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    End If
    ActiveCell.Offset(1, 1).Select
    Loop
    End Sub

    I'm sending the file also, if you want to have a look.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort routine in a macro (XP)

    Here is a version that sorts. It doesn't select any cells:

    Sub Alternative()
    Dim lngCol As Long
    For lngCol = Range("IV3").End(xlToLeft).Column To 3 Step -1
    Range(Cells(3, lngCol), Cells(65536, lngCol).End(xlUp)).Sort Key1:=Cells(3, lngCol), _
    Order1:=xlDescending, Header:=xlNo
    Cells(3, lngCol).EntireColumn.Insert
    With Cells(2, lngCol)
    .Value = "Rank"
    .Font.Bold = True
    End With
    Next lngCol
    End Sub

    Added: by sorting this way, the relation between column A and the other columns will be broken. So perhaps this is not what you intended.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort routine in a macro (XP)

    Your last sentence is correct. The relationship must be maintained. What I am trying to accomplish is to sort a column Z-A, copy the rank from the rank page (1 to 210, with 1 being the highest number) and paste that rank into the newly inserted Rank column. i then need to move to the next column, sort Z-A, and paste again. In this way the sort does not corrupt the data.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort routine in a macro (XP)

    In that case, replace

    Range(Cells(3, lngCol), Cells(65536, lngCol).End(xlUp)).Sort Key1:=Cells(3, lngCol), _
    Order1:=xlDescending, Header:=xlNo

    by

    Range("A2").CurrentRegion.Sort Key1:=Cells(3, lngCol), _
    Order1:=xlDescending, Header:=xlYes

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort routine in a macro (XP)

    Hans,

    I did replace and I see that it will sort the column, but this code is a little over my head and I can't get the next bit to work, so I'm asking your help. Once the column is sorted, I need the column to be inserted to the right of the sorted column and I need to copy and paste the Rank Page into that newly inserted column. The rank page is the other sheet in the workbook. This also eliminates the need for the word "Rank" to be typed in the first cell of the column.

    Here is the workbook again, with the modified code Sub Alternative.....

    Thanks for you help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort routine in a macro (XP)

    Here is a new version:

    Sub Alternative()
    Dim lngCol As Long
    With Worksheets("DMA Totals-1")
    For lngCol = 2 To 14 Step 2
    ' Sort on column
    .Range("A2").CurrentRegion.Sort Key1:=.Cells(3, lngCol), _
    Order1:=xlDescending, Header:=xlYes
    ' Insert column to the right
    .Cells(3, lngCol + 1).EntireColumn.Insert
    ' Copy rank column into new column
    Worksheets("Rank page").Columns(1).Copy Destination:=.Columns(lngCol + 1)
    Next lngCol
    End With
    End Sub

    It doesn't matter which worksheet is selected when you run this code. I have attached the modified version (zipped).

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort routine in a macro (XP)

    Hans et. al.,

    Hope your week-end was a pleasant one. It's getting a little colder here with crisp autumn air. Beautiful.

    The plot thickens.....Thank you for inserting the comments that show me what's going on. I was able to interpret those lines. I understand that lngCol is a variable that you are defining using "long". I also understand that you are repeating this column insertion 14 times by the Step 2 to 14. What does the following Step 2 mean? Actually, how is step used?

    The problem this presents is that this spreadsheet goes all the way to column BX, and it's width is not going to be consistant. How can we adjust this macro to repeat itself from the far right (xlright??) and go to the left, yet keeping the integrity of the data?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort routine in a macro (XP)

    It's greyish here, and relatively mild.

    The code in the workbook I posted proceeds from left to right. Because a new column is inserted, the next column to be sorted is two columns to to the right of the column on which you just sorted. That is the reason for the Step 2. This also means that in fact, you are not inserting 14 times, but 7 times (=14/2)

    To make the macro more flexible, change the upper limit 14. If there are n columns, you want to insert n-1 columns (you don't insert one to the right of column A). So the loop goes from 2 to 2*(n-1). We can determine n by moving left from cell IV2 until we encounter a non-blank one. This is Range("IV2").End(xlToLeft).Column. The For ... line becomes:

    For lngCol = 2 To 2 * (.Range("IV2").End(xlToLeft).Column - 1) Step 2

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort routine in a macro (XP)

    Hans,

    Thanks for suffering through my questions and working with me to make an end user happy. Everything is working as you said.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •