0

I have copied the dates from sheet Query and pasted to sheet Projection. I need to get dates pasted in ascending order but the code gives me random order. Can you fix my code to get correctly ordered dates? Thanks in advance!

This is my current output

enter image description here

Code is below:

Sub code()
    Sheets("Projection").Cells.Clear
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Query")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Projection")
    Dim lRow As Long, x As Long, lRow2 As Long, i As Long, c As Long
    Dim dts As Variant
           
    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    dts = ws1.Range("D2:D" & lRow) 

    With CreateObject("Scripting.Dictionary")
        For x = LBound(dts) To UBound(dts)
            If Not IsMissing(dts(x, 1)) Then .Item(dts(x, 1)) = 1
        Next
        dts = .Keys
    End With

    ws2.Range("C1").Resize(, UBound(dts) + 1) = dts 
    ws1.Range("A1:B" & lRow).Copy ws2.Range("A1") 
    ws2.Range("A1:B" & lRow).RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo 
    lRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
  
    For c = 3 To 3 + UBound(dts)
        For i = 2 To lRow2
            ws2.Cells(i, c) = Application.WorksheetFunction.SumIfs _
            (ws1.Range("F:F"), ws1.Range("D:D"), ws2.Cells(1, c), _
            ws1.Range("B:B"), ws2.Range("B" & i)) 
        Next
    Next
    ws2.Columns.AutoFit
End Sub
PeterT
  • 8,232
  • 1
  • 17
  • 38
kuldar
  • 1
  • 1
  • You can sort `dts` before adding it to the sheet https://stackoverflow.com/questions/152319/vba-array-sort-function – Tim Williams May 05 '21 at 17:14
  • Hi Peter, thank you for advice! I already tried to sort dts after your comment but unfortunately with no luck. Can you please change my code above? – kuldar May 06 '21 at 05:56

1 Answers1

0

Copy the BubbleSort Sub from https://wellsr.com/vba/2018/excel/vba-bubble-sort-macro-to-sort-array/

Then do this:

'...
'...
BubbleSort dts
ws2.Range("C1").Resize(, UBound(dts) + 1) = dts 
'...
'...
Tim Williams
  • 154,628
  • 8
  • 97
  • 125