1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA-Excel Programming sub code!

Discussion in 'VBA Macros' started by Johan1959, Mar 25, 2017.

  1. Johan1959

    Johan1959 New Member

    Messages:
    14
    Hi There,
    VBA Excel Programming problem!

    I am having an Add Button on a form. When Clicked, two txtBoxes and a listBox values are written onto a spreadsheet into Column A ("Numbers"), Column D("Size") and Column E("Time") as AM/PM.
    Column A gets any sort off numbers like 1,2,6,7B,7A, 21,22,23, 18B,18C,18A, ......33,35,27A,27B, till last number.
    Then I split, “with a split text coding in a Module" the values in column A to Column B (only Numbers) and Column C (only letters) which I hide from the client.


    The Problem:-
    Now I want to Automatically after CLICKING the ADD BUTTON, sort Column B(Numbers) in ascending order from small to large and Column C(Letters) to follow in ascending order but to KEEP IT'S position or place with their corresponding Number.
    And then Lastly to sort Column A, Column D and Column E using the info from columns B:C

    The Data in Column A , D and E will be used elsewhere in the program for the customer to see on a Form but in an ASCENDING Order!!

    I have had looked to most Forums for this solution but yet to find my degree of problem solving.
    Hope someone is able to assist me.
    Thx in advance.
    Chirag R Raval likes this.
  2. vletm

    vletm Well-Known Member

    Messages:
    2,399
    @Johan1959
    Are You sure that someone really could/would help WITHOUT even sample file?
  3. Johan1959

    Johan1959 New Member

    Messages:
    14
    Hi vletm,

    To real programmers this should be easy as eating a cake!

    It's actually simply, there's a spreedsheet called "house" with column A,B,C,D,E and column B and C will be hiding from client.
    Then in developer and then Visual Basic tab, viewing the Forms and when clicked on the "house" form it show you the form that the client would see to put in the numbers in txtBox, the size in txtBox and the time ListBox(AM/PM). There's an Add Button _Click to activate the above.

    The numbers, size and time is then written onto the spreadsheet "house" in last Row.

    Now I want to call a sub that (My Problem lies here), to sort order all 5 columns in ascending order. The problem is that Excel nor VBA do a Automatic ascending order to put the numbers as it should be. Like in column A the first number will always be 1 then 2, 3, 4, 5,....(but it does not put 7A, 7B in ascending order), it through this numbers to the last row. And so does it with all the number that got an letter attached.

    I split the numbers in to column B and the letters into column C with a split function.

    After the split I want to call the sub to sort out all columns into ascending order using column B sort it out in ascending order then Column C in ascending order but keeps it position or place with the number and then to sort out Column A, D, and E using B and C.

    I can do this by using the ribbon and click on DATA and use the Sort tab and do the ascending order and it works fine, but I'm sure there should be an Automatic VBA coding for this.

    I did try to code using other info from the web and try to re-write it but yet to find my ASCENDING ORDER.

    Further more i can't show you anything to help you. The code's that I can show got nothing to do with this problem.

    Just need a simple call method to sort the numbers in ascending order. Remember the numbers are not alone, it's mixed like 18A, 18B, 18C , 22,23,25,27A, 27B and this is part of the problem I have.

    Sorry for this long letter.

    Regards
    Last edited: Mar 27, 2017
  4. vletm

    vletm Well-Known Member

    Messages:
    2,399
    @Johan1959
    To real programmers this should be easy as eating a cake!
    Yes!
    I have eaten a lot of cake as well done many more things.

    You asked:
    Just need a simple call method to sort the numbers in ascending order.
    So, I closed my eyes and ...
    there were this kind of code:
    Code (vb):

    Sub Johan1959()
        a_tab = ActiveSheet.Name   ' set Sheet as You need
       Rng = "B3:B8"                     ' set Your range as need
       With Sheets(a_tab).Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range(Rng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range(Rng)
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
     
    PS. There are no problems ... only challenges which can sometimes solve!
    sathishsusa and Marc L like this.
  5. Johan1959

    Johan1959 New Member

    Messages:
    14
    Hi vletm,

    Thx mate, will try the code but sure I'll have tried it before with no luck.

    Maybe a mistake or bad cake.

    Just teasing.
  6. jindon

    jindon Well-Known Member

    Messages:
    503
    Johan1959,

    I wrote this some years back.

    See if this is kind of what you are trying to do.
    Code (vb):

    Sub test()
        Dim x, i As Long
        x = [{"7B";"7A";21;22;23;"18b";"18C";6; 2;1;"7B";"7A";"18A";33;35;"27A";"27B"}]
        With Cells(1).Resize(UBound(x), UBound(x, 2))
            .Value = x
            MsgBox "Randomly set the value in colA"
            ReDim Preserve x(1 To UBound(x), 1 To 2)
            For i = LBound(x, 1) To UBound(x, 1)
                x(i, UBound(x, 2)) = GetSortVal(UCase$(x(i, 1)))
            Next
            VSortM x, LBound(x, 1), UBound(x, 1), UBound(x, 2)
            .Value = x
        End With
    End Sub

    Function GetSortVal(ByVal txt As String) As String
        Static RegX As Object
        Dim i As Long, m As Object
        If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExP")
        With RegX
            .Pattern = "\d+"
            If .test(txt) Then
                For i = .Execute(txt).Count - 1 To 0 Step -1
                    Set m = .Execute(txt)(i)
                    txt = Application.Replace(txt, m.firstindex + 1, _
                    m.Length, Format$(m.Value, String(10, "0")))
                Next
            End If
        End With
        GetSortVal = txt
    End Function

    Sub VSortM(ary, LB, UB, ref)
        Dim i As Long, ii As Long, iii As Long, m, temp
        i = UB: ii = LB
        m = ary(Int((LB + UB) / 2), ref)
        Do While ii <= i
            Do While ary(ii, ref) < m: ii = ii + 1: Loop
            Do While ary(i, ref) > m: i = i - 1: Loop
            If ii <= i Then
                For iii = LBound(ary, 2) To UBound(ary, 2)
                    temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
                    ary(i, iii) = temp
                Next
                i = i - 1: ii = ii + 1
            End If
        Loop
        If LB < i Then VSortM ary, LB, i, ref
        If ii < UB Then VSortM ary, ii, UB, ref
    End Sub
  7. Johan1959

    Johan1959 New Member

    Messages:
    14
    Hi jindon,

    Thx mate, will try it too.

    Question, for x = the numbering is some text that the customer put into txtBox and very from 1 to 100. But some numbers are attach with letters. So this
    x = [{"7B";"7A";21;22;23;"18b";"18C";6; 2;1;"7B";"7A";"18A";33;35;"27A";"27B"}], I'll will never now how many numbers will be entered from customer to customer. Some customers could only have the normal numbers but my problem at present, the customer wants to sort ascending order his input which is like the sample.

    Don't really know how to do it.
  8. jindon

    jindon Well-Known Member

    Messages:
    503
    Just test it so that you will see how it sorts the data.

    By the way, x is a an array consists of Numeric value and string value and it sorts as

    1
    2
    6
    7A
    7A
    7B
    7B
    18A
    18b
    18C
    21
    22
    23
    27A
    27B
    33
    35
    Johan1959 likes this.
  9. vletm

    vletm Well-Known Member

    Messages:
    2,399
    @Johan1959
    My code works and my cake is good!
    Why so negative?
  10. Johan1959

    Johan1959 New Member

    Messages:
    14
    vletm and jindon,

    Today I am going to put both your code to the test and let you no of my finding.

    Thanks for helping.

    Regards
  11. Johan1959

    Johan1959 New Member

    Messages:
    14
    Hi vletm, still try to work on your code. Was not working the first time.
  12. vletm

    vletm Well-Known Member

    Messages:
    2,399
    @Johan1959 ... hmmm
    and You asked:
    Just need a simple call method to sort the numbers in ascending order.
    ... and I gave it - sort numbers! Excel do not make mistakes ...
    Is this again one case then someone write about numbers which are not numbers and someone has sheet where is almost all ready but ...
    If I give another sample, then someone would day 'no' ...
    because someone has just there something else...
    or
    if my sample would sort two columns or more ...
    then whatever would happen someone's sheet ...
    That's why a sample file would be easier for You!
  13. Johan1959

    Johan1959 New Member

    Messages:
    14
    Just need a simple call method to sort the numbers in ascending order.
    ... and I gave it - sort numbers! Excel do not make mistakes ...


    And you did not read my previous message through!!
    which however mentioned mixed numbers (Numbers + letters) or (letters + numbers) thou...

    I'm not an VBA expert but try to put something together. My program works well except for some reason I can't figure out this problem!

    But thanks for replying therefore I will keep struggling till sorted!!!
  14. vletm

    vletm Well-Known Member

    Messages:
    2,399
    and You also wrote...
    ... Then I split, “with a split text coding in a Module" the values in column A to Column B (only Numbers) and Column C (only letters) which I hide from the client. ... Is there something else which You should figure?
  15. jindon

    jindon Well-Known Member

    Messages:
    503
    Johan1959

    If you can not adopt my code to your range then just try change the "test" sub procedure like below while other procedures remain intact.
    Code (vb):

    Sub test()
        Dim a, i As Long
        Const KeyCol As Long = 1
        Rem KeyCol is a relative column position within a range
        Rem If range starts from Col.C then 1 = C, 2=D...
        With Range("a1:g18")  '★ change the range
           a = .Value
            ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
            For i = LBound(a, 1) To UBound(a, 1)
                a(i, UBound(a, 2)) = GetSortVal(UCase$(a(i, KeyCol)))
            Next
            VSortM a, 2, UBound(a, 1), UBound(a, 2)
            .Value = a
        End With
    End Sub
  16. Johan1959

    Johan1959 New Member

    Messages:
    14
    Hi jindon, the problem with the an array is that it list the numbers as is within the array in the same order. It's suppose to re-order in ascending order column A based on the ascending order in column B and secondly the ascending order from column C. Column C which consist of letters only to keep it's position with the number in Column B.
    Thirdly Column A, D and E to follow .
  17. Johan1959

    Johan1959 New Member

    Messages:
    14
    I will put this to the test.
  18. Johan1959

    Johan1959 New Member

    Messages:
    14

    Hi vletm, if I use the normal excel ribbon and used the sort order tab and set Column B as key1 and Column C as key2 then it works perfectly. It re-order column B into ascending order then column C into ascending order and then Column A, D and E to follow.
    But:-
    I re-code the same code into VBA but it's not working. It add empty rows in row below heading and then it only do the ascending order in column C. Somehow I may have a problem.

    Unfortunately I can't copy past my full code as I'm working on a second laptop not connected with the internet for security reasons.
  19. vletm

    vletm Well-Known Member

    Messages:
    2,399
    Johan1959
    You can record that 'sorting' and after that use it (maybe edit some parts away)!
    Are You headers in 1st row?
    And
    Is there data only from A- to E-columns?
    Do You need 'a piece of cake'?
  20. jindon

    jindon Well-Known Member

    Messages:
    503
    My guess...
    If you upload a small sample workbook with before/after, it can be easily done.
    Code (vb):

    Sub test()
        Dim a, e, txt As String, i As Long
        Const KeyCol As String = "2,3,1,4,5"  '<- columns to sort in order
       With Cells(1).CurrentRegion
           a = .Value
            ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
            For i = LBound(a, 1) To UBound(a, 1)
                For Each e In Split(KeyCol, ",")
                    txt = txt & Chr(2) & a(i, e)
                Next
                a(i, UBound(a, 2)) = GetSortVal(UCase$(txt)): txt = vbNullString
            Next
            VSortM a, 2, UBound(a, 1), UBound(a, 2)
            .Value = a
        End With
    End Sub
  21. vletm

    vletm Well-Known Member

    Messages:
    2,399
    @Johan1959
    Without ... cake sample ... You got this!
    As You have told that You have already split values for B- & C-columns.
    Code (vb):

    Sub Johan1959_Sorts()
        Application.ScreenUpdating = False
        With ActiveSheet
            b_max = .Cells(.Rows.Count, 2).End(xlUp).Row
        End With
        With ActiveSheet.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("B2:B" & b_max), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=Range("C2:C" & b_max), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:E" & b_max)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Application.ScreenUpdating = True
    End Sub
     

    Nobody else cannot know, what You really have there!
  22. Johan1959

    Johan1959 New Member

    Messages:
    14
    vletm, thx mate, the cake is good!
  23. Johan1959

    Johan1959 New Member

    Messages:
    14
    a(i, UBound(a, 2)) = GetSortVal(UCase$(txt)): txt = vbNullString
    Next

    Hi jindon, I have tried your code, although the data entered into the txtBoxes and listBox are written to the lastRow on spreadsheet, the code stops at GetSortVal as it gives me a compile error. "Sub or Function not defined"

    Into what should I define this function?

    I still want to test your code as this helps me understand VBA and help coding.

    Thx in advanced!
  24. jindon

    jindon Well-Known Member

    Messages:
    503
    That means you deleted the function code "Function GetSortVal" that I have posted.
    And you might also delete VSortM sub procedure...
  25. Johan1959

    Johan1959 New Member

    Messages:
    14

    Got you, Now I click for the first time what you try to given me. Thx again.
    Chirag R Raval likes this.

Share This Page