Sorting an Array "Subscript out of Range"

AliCat

Board Regular
Joined
Aug 8, 2002
Messages
82
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
40,675
Office Version
365
Platform
Windows
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
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
You Are well come
Any time
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,675
Office Version
365
Platform
Windows
@mohadin
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
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
@mohadin
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,531
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top