Sorting Array

[kirkm]

I have a 2 element variant array where

(0,x) is a date
(1,x) is a Number.

How could I sort this in VBA by date ascending and will this keep the right Number with its date ?

Thanks

 

[jleach]

Not sure if he has anything for this specific scenario, but Chip Pearson has an amazing set of Array tools:

http://www.cpearson.com/excel/vbaarrays.htm (epic - must have)
http://www.cpearson.com/excel/SortingArrays.aspx
http://www.cpearson.com/excel/SortingArraysOfObjects.htm

hth

 

[arnelgp]

here is a function.
copy and paste it in Standard
Module. Your array must be
Declared as Dynamic array and
not a Fixed array.

Option Compare Database
Option Explicit

Public Function Sort2DArray(ByRef arrVariant As Variant, intColumnToSort As Integer) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' arnelgp
' 21-Dec-2017
'
' Parameters
'
'   arrVariant      = the array to sort. this array MUST be Dynamically declared, eg:
'                     Dim arr() As Variant
'                     Redim arr(4,1)
'
'   intColumnToSort = which column to sort. i will follow
'                     the Base 1 option, which is
'                     1 means first column, 2 means the second column, etc.
'
' Returns
'
'   Sorted array
'
' Some Info:
'
'   two dimension arrays are in this format:
'       arrName(numberOfRows, numberOfColumns)
'
'   on normal (Base 0) this means: arrName(numberOfRows, 1)
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim lLoop1 As Long
    Dim lLoop2 As Long
    Dim lTemp As Variant
    Dim lTemp2 As Variant
    
    Dim intOtherColumn As Integer
    
    intOtherColumn = Choose(intColumnToSort, 2, 1)
    
    For lLoop1 = UBound(arrVariant, 1) To LBound(arrVariant, 1) Step -1
      For lLoop2 = LBound(arrVariant, 1) + 1 To lLoop1
        If arrVariant(lLoop2 - 1, intColumnToSort - 1) > arrVariant(lLoop2, intColumnToSort - 1) Then
          lTemp = arrVariant(lLoop2 - 1, intColumnToSort - 1)
          lTemp2 = arrVariant(lLoop2 - 1, intOtherColumn - 1)
          arrVariant(lLoop2 - 1, intColumnToSort - 1) = arrVariant(lLoop2, intColumnToSort - 1)
          arrVariant(lLoop2 - 1, intOtherColumn - 1) = arrVariant(lLoop2, intOtherColumn - 1)
          
          arrVariant(lLoop2, intColumnToSort - 1) = lTemp
          arrVariant(lLoop2, intOtherColumn - 1) = lTemp2
        End If
      Next lLoop2
    Next lLoop1
    Sort2DArray = arrVariant

End Function

Private Sub test()
Dim j As Integer
Dim i() As Variant
ReDim i(4, 1)
i(0, 0) = #1/15/2017# 'a
i(1, 0) = #1/25/2017# 'b
i(2, 0) = #1/13/2017# 'c
i(3, 0) = #1/4/2017#  'd
i(4, 0) = #1/5/2017#  'e
i(0, 1) = "a"
i(1, 1) = "b"
i(2, 1) = "c"
i(3, 1) = "d"
i(4, 1) = "e"
i = Sort2DArray(i, 1)
For j = 0 To UBound(i, 1)
    Debug.Print i(j, 0), i(j, 1)
Next

End Sub

 

[kirkm]

Thanks arnelgp and Jack, that's superb and is working perfectly. I just had to reverse the dimensions. You example really helped there. Cheers !