Geek Answers Handbook

How can I sort dates in an array in vba?

Hi, I am new to programming and just started learning VBA for Excel. I have a request to sort arrays. How to sort an array containing dates? For example, if I have an array containing dates ("23-jul-13", "11-jan-10", "1-may-09", "3-feb-04"), how can I sort this array. I searched the Internet for answers, but could find code to sort numbers. I tried my brains on this for 2 days, but didn't seem to be able to get it.

thanks

I have the code below that takes dates from a selected column, but I get an error when I run it. I am trying to find out what is wrong with him for 2 days. I didn’t mention this code before, because although it would not be superfluous to add to the confusion. Sub GetUniqueAndCount works fine, but this is sorting, which is a problem since it does not accept the array passed to it as an argument.

Sub GetUniqueAndCount()
Dim d As Object, c As Range, k, tmp As String

  Set d = CreateObject("scripting.dictionary")
  'I will select the column of dates
 For Each c In Selection
  tmp = Trim(c.Value)
  If Len(tmp) > 0 Then
  If Year(DateValue(Format(tmp, "dd-mmm-yy"))) = 2013 Then
  d(tmp) = d(tmp) + 1
  End If
  End If
  Next c
  i = 0
  ReDim ThisArray(UBound(d.keys)) As Date
  For Each k In d.keys
  ThisArray(i) = DateValue(Format(k, "dd-mmm-yy"))
  i = i + 1

  Next k
  Sort (ThisArray)
End Sub


Sub Sort(arr() As Date)

  Dim Temp As Date
  Dim i As Long
  Dim j As Long

  For j = 2 To UBound(arr)

  Temp = arr(j)
  For i = j - 1 To 1 Step -1
  If (arr(i) <= Temp) Then GoTo 10
  arr(i + 1) = arr(i)

  Next i
  i = 0
10  arr(i + 1) = Temp


  Next j
  End Sub
+4
source share
2 answers

Yours is Sort(arr() As Date)working fine. The problem is this line

Sort (ThisArray)

Change it to

Sort ThisArray

Also, since you save Datesin ThisArray, I hope you declared it as Date?

Example

Sub Sample()
    Dim ThisArray(1 To 5) As Date

    ThisArray(1) = #12/13/2013#
    ThisArray(2) = #12/13/2012#
    ThisArray(3) = #12/13/2015#
    ThisArray(4) = #12/13/2014#
    ThisArray(5) = #12/13/2016#

    SortAr ThisArray

    For i = 1 To 5
        Debug.Print ThisArray(i)
    Next i
End Sub

Sub SortAr(arr() As Date)
    Dim Temp As Date
    Dim i As Long, j As Long

    For j = 2 To UBound(arr)
        Temp = arr(j)
        For i = j - 1 To 1 Step -1
            If (arr(i) <= Temp) Then GoTo 10
                arr(i + 1) = arr(i)
        Next i
        i = 0
10:     arr(i + 1) = Temp
    Next j
End Sub

OUTPUT

13/12/2012 
13/12/2013 
13/12/2014 
13/12/2015 
13/12/2016
+6

. .

Sub Sort()
Dim x As Long, y As Long, z As Long
    For x = Application.WorksheetFunction.Min(Columns("M")) To Application.WorksheetFunction.Max(Columns("M"))
        For y = 1 To Worksheets("Users Info").Cells(Rows.Count, 13).End(xlUp).Row
        If Worksheets("Users Info").Cells(y, 13).Value = i Then
            z = z + 1
            Worksheets("Users Info").Cells(z, 14).Value = i
        End If
        Next y
    Next x
End Sub
0

More articles:


All Articles