# Sorting an Array "Subscript out of Range"

#### AliCat

##### Board Regular
Can anyone help me with what I am not understanding regarding this bubble sort. This is just the start of my learning curve and I'm falling at the first hurdle. This is a one dimensional array with 762 rows in a single column. Am I getting mixed up with the operation of rows and columns here or is it something else. I get a "subscript out of range" as soon as I refer to the first entry in the array - I've attached comments of output at various points in my code.
Thanks.

Code:
``````Sub bSort1D(Optional iArr As Variant)
'// Sorts the 1D input Array in ascending order
'// not yet solved  ?? do I need to transpose the array first ??
Dim arr()
cr = vbCrLf
' Range("Test2018") = range E5:E766
' first 6 values are:   221, 293, 231, 231, 191, 103
stime = Now()
arr = Range("Test2018")                 ' frec = 1 to lrec = 762
Dim FRec As Long, LRec As Long    ' FirstRecord, LastRecord
Dim tem As Long
Dim j As Integer, i As Integer
FRec = LBound(arr)                     ' 1
LRec = UBound(arr)                     ' 762
MsgBox "FRec: " & FRec & cr & "LRec: " & LRec
Debug.Print LBound(arr, 1), UBound(arr, 1), LBound(arr, 2), UBound(arr, 2)         ' output: 1, 762, 1, 1
'Debug.Print arr(1), arr(2)                ' subscript out of range (9)

For i = FRec To LRec - 1               ' i = 1 to 762 -1
For j = i + 1 To LRec                  ' j = 1+1 to 762
MsgBox i & cr & j                       ' i = 1, j = 2
'MsgBox arr(i) & cr & arr(j)          ' subscript out of range (9)
If arr(i) > arr(j) Then               ' i = 1, j = 2  : 221 v 293         'subscript out of range (9)
tem = arr(j)
arr(j) = arr(i)
arr(i) = tem
End If
Next j
Next i
Selection.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
etime = Now()
title = "bSort1D"
msg = "Done" & cr & cr
msg = msg & "Start:  " & stime & cr
msg = msg & "End:    " & etime & cr
msg = msg & "Duration:  " & etime - stime & cr
MsgBox msg, vbInformation + vbOKOnly, title
End Sub``````

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Fluff

##### MrExcel MVP, Moderator
When loading an array from a sheet you will end up with a 2D array, not a 1D array.
try
Code:
``````      If arr(i,1) > arr(j,1) Then               ' i = 1, j = 2  : 221 v 293         'subscript out of range (9)
tem = arr(j,1)
arr(j,1) = arr(i,1)
arr(i,1) = tem
End If``````

• AliCat

##### Active Member
Hi
Or

If your array is one dimensional
arr=range("test2018") this gives 2d array
so you have to transpose it to get 1d array
arr=Application.Transose(arr)
Then you do'nt need to amend you code but one line code

• AliCat

#### AliCat

##### Board Regular
Thank you. I now understand.

##### Active Member
You Are well come
Any time

#### Fluff

##### MrExcel MVP, Moderator
With your suggestion, the code will most likely crash on this line
Code:
``Selection.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr``
@AliCat
Glad to help & thanks for the feedback

##### Active Member
With your suggestion, the code will most likely crash on this line
Code:
``Selection.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr``
Code:
``Selection.Resize(ubound(ar)) = Application.Transpose(ar)`` Last edited: