Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Alphabetical Sorting in VBA

    I want to sort alphabetically only from first empty cell in [F] to [W47]. This is what I tried so far with no luck

    Code:
        '...................................................................
        If Target.Column = [k1].Column Then                                 'Qty changed..
        '...................................................................
        
            iName = Target.Offset(0, -1)
            LID = Target.Offset(0, 4)                                       'LID = 3
            Call FindAccount(LID)
            
            Application.EnableEvents = False
            '.............................'
            'Update item Qty on Accounts..'
            '.............................'
            With ActiveSheet
                For I = 3 To 47 Step 1                              'Check every 24 steps down, starting at 2
                    If .Cells(I, "F") = "" Then Exit Sub            'Exit at the first empty cell
                    If .Cells(I, "F") = iName Then                  'Check for match of Item name
                        .Cells(I, "G") = Target                     'Update Qty
                        '-----------------------------------
                        If .Cells(I, "G") = "0" Then                'Item Name cell in column F is empty so..
                            Application.EnableEvents = False
                            '-----------------------------------
                            Temp = "F" & I & ":K" & I               'e.g. temp="F23:K23"
                            .Range(Temp).ClearContents   'clear Temp
                            '-----------------------------------
                            Dim lStartRow As Long                   'sort the tab alphabetically from the deleted row to the end..
                            Dim lEndRow   As Long
                            lStartRow = I - 1
    
    [Error: Short Method of Range Class Failed]
                            '.Range("F" & lStartRow & ":W47").Sort key1:=["F" & lStartRow], Header:=xlYes, Order1:=xlAscending
    
    [Error: The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.]
                            '.Range("F" & lStartRow & ":W47").Sort key1:=[F2], Header:=xlYes, Order1:=xlAscending
    
                           Exit Sub
    Any thoughts on why this won't work? Here is a screenshot of what the sheet looks like when the alphabetical sorting is initiated.

    Sample.jpg


    Thanks for looking...
    Ferenc
    Last edited by Ferenc Nagy; 2015-07-24 at 14:32.

  2. #2
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    and the solution was:

    Code:
    .Range("F" & lStartRow & ":W47").Sort key1:=.Range("F" & lStartRow & W47), Header:=xlYes, Order1:=xlAscending

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Ferenc

    You need to take your sort snippet out of the "I" loop otherwise, you will be sorting unnecessarily within each loop.

  4. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Ferenc

    You need to take your sort snippet out of the "I" loop otherwise, you will be sorting unnecessarily within each loop.

    The sorting is withing an If Statement, so only happens if the Qty that was just updated, happens to be Zero... otherwise it exits the loop... Just didn't include the end in the snippet here.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    and the solution was:

    Code:
    .Range("F" & lStartRow & ":W47").Sort key1:=.Range("F" & lStartRow & W47 ),Header:=xlYes, Order1:=xlAscending
    Ferenc,

    Not sure how your solution could possibly work. (In blue) You are missing the colon and quotes and also your key should be a column range not the entire range of the sort. You are sure to receive a Range and sort error here.

    I would think...

    .Range("F" & lStartRow & ":W47").Sort key1:=.Range("F" & lStartRow & ":F47" ),Header:=xlYes, Order1:=xlAscending

    ...or am I missing something?

  6. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Ferenc,

    Not sure how your solution could possibly work. (In blue) You are missing the colon and quotes and also your key should be a column range not the entire range of the sort. You are sure to receive a Range and sort error here.

    I would think...

    .Range("F" & lStartRow & ":W47").Sort key1:=.Range("F" & lStartRow & ":F47" ),Header:=xlYes, Order1:=xlAscending

    ...or am I missing something?

    You are exactly right. I wrote it correctly in the first half of the line and wrong after. I figured this out and fixed it right away, just forgot to come back here and correct it.

Posting Permissions

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