• 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...

  • 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!

Johan1959

New Member
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.
 
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:
@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:
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!
 
Johan1959,

I wrote this some years back.

See if this is kind of what you are trying to do.
Code:
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
 
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.
 
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
 
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
 
@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:
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!
Hi vletm, still try to work on your code. Was not working the first time.
 
@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!
 
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!!!
 
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?
 
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:
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
 
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 .
 
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?


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.
 
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'?
 
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 .
My guess...
If you upload a small sample workbook with before/after, it can be easily done.
Code:
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
 
@Johan1959
Without ... cake sample ... You got this!
As You have told that You have already split values for B- & C-columns.
Code:
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!
 
@Johan1959
Without ... cake sample ... You got this!
As You have told that You have already split values for B- & C-columns.
Code:
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!

vletm, thx mate, the cake is good!
 
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!
 
Back
Top