I'm using a spreadsheet to input a series of one and two digits into a particular cell. I want to be able to run a macro (via VBA code) that will sort my input into chronological / ascending order. For example, I input the numbers: 21, 10, 37, 2, 5, 44 into cell A1; the numbers will be separted by a space not a comma (,). After running the macro, cell B2 (or whatever cell) should show: 2 5 10 21 37 44. Please help!
Also, in the same spreadsheet I want to be able to count the number of times I press the "F9" key. In the spreadsheet I have a series of calculations that excel is preforming, and by pressing "F9" the calculations are updated automatically. Please help!
Regards.
Sorting Data (Numbers) Within a Single Cell
-
-
-
Re: Sorting Data (Numbers) Within a Single Cell
Hi,
There may be better ways..
Code
Display MoreFunction SORTNUM(ByRef v) As String Dim i As Long, j As Long, t If TypeOf v Is Range Then v = v.Value v = Split(v, ",") For i = 0 To UBound(v) For j = i To UBound(v) If Val(v(j)) < Val(v(i)) Then t = v(i) v(i) = v(j) v(j) = t End If Next Next SORTNUM = Join(v, " ") End Function
use
=SORTNUM(A1)
-
Re: Sorting Data (Numbers) Within a Single Cell
Thanks!
For whatever reason the code is not sorting the numbers; it's only copying the input! -
Re: Sorting Data (Numbers) Within a Single Cell
I've tried several times to get the code to perform as I need it to. Now I'm getting the error #NAME?
Please help -
Re: Sorting Data (Numbers) Within a Single Cell
This UDF should help. Note that the optional Delimiter argument defaults to space.
Code
Display MoreFunction sortedString(unSortedString As String, Optional Delimiter As String = " ", Optional Descending As Boolean) As String Dim unSortedArray As Variant, i As Long Dim SortedArray As Variant Dim pivot As Double, Left As String, Right As String unSortedArray = Split(unSortedString, Delimiter) Debug.Print LBound(unSortedArray) & ":" & UBound(unSortedArray) If UBound(unSortedArray) <= 0 Then sortedString = unSortedString Else pivot = Val(unSortedArray(0)) For i = 1 To UBound(unSortedArray) If (Val(unSortedArray(i)) < pivot) Xor Descending Then Left = Left & Delimiter & unSortedArray(i) Else Right = Right & Delimiter & unSortedArray(i) End If Next i Left = sortedString(Mid(Left, Len(Delimiter) + 1), Delimiter, Descending) Right = sortedString(Mid(Right, Len(Delimiter) + 1), Delimiter, Descending) sortedString = pivot If Left <> vbNullString Then sortedString = Left & Delimiter & sortedString End If If Right <> vbNullString Then sortedString = sortedString & Delimiter & Right End If End If End Function
Also for the F9 thing. Try this UDF
-
-
Re: Sorting Data (Numbers) Within a Single Cell
Hi,
Try this version. It has an option to provide the delimiter as well.
Code
Display MoreFunction SORTNUM(ByRef v, Optional Delim As String = ",") As String Dim i As Long, j As Long, t If TypeOf v Is Range Then v = v.Value v = Split(v, Delim) For i = 0 To UBound(v) For j = i To UBound(v) If Val(v(j)) < Val(v(i)) Then t = v(i) v(i) = v(j) v(j) = t End If Next Next SORTNUM = Join(v, " ") End Function
use like
=SORTNUM(A1," ")
or
=SORTNUM(A1,",")
HTH
-
Re: Sorting Data (Numbers) Within a Single Cell
Another one:
http://www.mrexcel.com/forum/showthread.php?t=367994Code
Display MoreFunction OrderCell(sStr As String) Dim i As Long, ii As Long, a() ReDim a(1 To Len(sStr)) For i = 1 To Len(sStr)) a(i) = Mid$(sStr, i, 1) Next For i = 1 To UBound(a) - 1 For ii = i + 1 To UBound(a) If a(i) > a(ii) Then temp = a(ii) : a(ii) = a(i) : a(i) = temp End If Next Next OrderCell = Join(a, "") End Function
-
Re: Sorting Data (Numbers) Within a Single Cell
Each of the UDFs work to perfection! Thank you to you all!
Lastly, any advice you can provide as to how I can become as proficient as you in VBA programming. Please advise.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!