Trouble to sort an array in Excel using VBA

[choco140]

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]

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