Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble to sort an array in Excel using VBA
I have a column in a worksheet which let's say looks that way:
1 3 2 4 I would like to sort it in order to get: 1 2 3 4 I tried to adapt this funtion but couldn't get it work... Can anybody help me? I must miss something here... I always get #VALUE even using SHIFT+CTRL+ENTER Function BubbleSort(List As Variant) ' Sorts an array using bubble sort algorithm Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp As Integer First = LBound(List) Last = UBound(List) For Each Element In List For j = i + 1 To Last If List(i) List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j 'Next i Next Element End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble to sort an array in Excel using VBA
If you really meant you wanted the range sorted, record a macro when you sort
that range. If you wanted to retrieve the values from the range (into an array), then sort that array without touching the original range: Option Explicit Sub test1() Dim myArray As Variant Dim iCtr As Long With ActiveSheet myArray = Application.Transpose(.Range("a1", _ .Cells(.Rows.Count, "A").End(xlUp))) End With BubbleSort myArray For iCtr = LBound(myArray) To UBound(myArray) Debug.Print myArray(iCtr) Next iCtr End Sub Function BubbleSort(List As Variant) ' Sorts an array using bubble sort algorithm Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp As Integer First = LBound(List) Last = UBound(List) For i = 1 To Last - 1 For j = i + 1 To Last If List(i) List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next i End Function choco140 wrote: I have a column in a worksheet which let's say looks that way: 1 3 2 4 I would like to sort it in order to get: 1 2 3 4 I tried to adapt this funtion but couldn't get it work... Can anybody help me? I must miss something here... I always get #VALUE even using SHIFT+CTRL+ENTER Function BubbleSort(List As Variant) ' Sorts an array using bubble sort algorithm Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp As Integer First = LBound(List) Last = UBound(List) For Each Element In List For j = i + 1 To Last If List(i) List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j 'Next i Next Element End Function -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Trouble | New Users to Excel | |||
Trouble with Array multiple return formula | Excel Discussion (Misc queries) | |||
Having trouble getting MATCH to work with a variable lookup array | Excel Worksheet Functions | |||
sort & expand trouble | Excel Discussion (Misc queries) | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) |