• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting array of dates

J

joharmenezes

Member
How do I sort an array of dates in vba? I need a sub through which I can pass an array of dates as an argument. The array will have dates stored as [format(A1,"dd-mmm-yy")]

Thanks
 

If I ask ! :rolleyes:

I surely need a workbook 'cause often dates are not dates in Excel data types …

So, you could do by your own using the Macro recorder or you post a sample workbook !
It's up to you !​
 
Hi, joharmenezes!
Simply sort them as numbers, as they're numeric values representing dates: Data tab, Sort and Filter group, Sort icon, Order by Column A, From older to recent, Accept.
But don't ever expect to get them sorted as you used a volatile function like RANDBETWEEN to generate them.
Regards!
 
SirJB7 said:
Hi, joharmenezes!
Simply sort them as numbers, as they're numeric values representing dates: Data tab, Sort and Filter group, Sort icon, Order by Column A, From older to recent, Accept.
But don't ever expect to get them sorted as you used a volatile function like RANDBETWEEN to generate them.
Regards!
Click to expand...

I know I can sort them the easier way but I want to do it in vba. I think the cell values can be converted to strings and stored in an array. It's the array that I want sorted. I uploaded this workbook as the previous guy asked me to do it. You can ignore this workbook. Just consider an array with the values for example, ("1-Dec-12","31-Dec-13","21-Jun-11","1-May-06") and sort it.
 
SirJB7 said:
Hi, joharmenezes!

If you want to do it the hard way it's up to you:
a) From here:
http://chandoo.org/forum/threads/simple-database-build.13673/#post-80859
check SortSelected procedure
b) From here:
http://chandoo.org/forum/threads/alphanumeric-sorting-solved.10611/#post-61366
check SortAColumn procedure

Regards!
Click to expand...

The following is the code I have written for sorting. can u tell me what is wrong:

Code: 
Sub GetUniqueAndCount()

  Dim d As Object, c As Range, k, tmp As String

  Set d = CreateObject("scripting.dictionary")
  'I will select the columns 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
 
joharmenezes said:
How do I sort an array of dates in vba? I need a sub through which I can pass an array of dates as an argument. The array will have dates stored as [format(A1,"dd-mmm-yy")]
Click to expand...
Hi, joharmenezes!

The 1st wrong thing is what you posted when started the thread: you were asking for code to sort an array and you yet had one, working or not but you had. So people like me who spent a few minutes searching WTF code was, now have to work again to check your code. Next time please state clearly what you want to achieve, what you've done and where you're stumped... as it's clearly stated here:
http://chandoo.org/forum/forums/new-users-please-start-here.14/
http://chandoo.org/forum/threads/new-users-please-read.294/
""
How to get the Best Results at Chandoo.org
  • When drafting a question, try and lay out the question in a clear and concise way.
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
  • Don't leave out information more info is better
  • For the best/fastest results, Upload a Sample File using the "Upload a File" button at the bottom of the page.
The 2nd wrong thing is that in the procedure "Sort(arr() As Date)" you're only using a For...Next loop instead of two (the outer from 1 to N-1, the inner from outer+1 to N). It's a matter of logic and algorithms beyond the scope of this post -and forums- but it won't ever work. Further reading if interested in:
http://www.sorting-algorithms.com/
https://en.wikipedia.org/wiki/Sorting_algorithm
And the bible from Niklaus Wirth, Algorithms + Data Structures = Programs:
http://www.amazon.com/Algorithms-Structures-Prentice-Hall-Automatic-Computation/dp/0130224189

Regards!

PS: WTF stands for where the file... just in case.
 
SirJB7 said:
Hi, joharmenezes!

The 1st wrong thing is what you posted when started the thread: you were asking for code to sort an array and you yet had one, working or not but you had. So people like me who spent a few minutes searching WTF code was, now have to work again to check your code. Next time please state clearly what you want to achieve, what you've done and where you're stumped... as it's clearly stated here:
http://chandoo.org/forum/forums/new-users-please-start-here.14/
http://chandoo.org/forum/threads/new-users-please-read.294/
""
How to get the Best Results at Chandoo.org
  • When drafting a question, try and lay out the question in a clear and concise way.
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
  • Don't leave out information more info is better
  • For the best/fastest results, Upload a Sample File using the "Upload a File" button at the bottom of the page.
The 2nd wrong thing is that in the procedure "Sort(arr() As Date)" you're only using a For...Next loop instead of two (the outer from 1 to N-1, the inner from outer+1 to N). It's a matter of logic and algorithms beyond the scope of this post -and forums- but it won't ever work. Further reading if interested in:
http://www.sorting-algorithms.com/
https://en.wikipedia.org/wiki/Sorting_algorithm
And the bible from Niklaus Wirth, Algorithms + Data Structures = Programs:
http://www.amazon.com/Algorithms-Structures-Prentice-Hall-Automatic-Computation/dp/0130224189

Regards!

PS: WTF stands for where the file... just in case.
Click to expand...

Well not my fault. The last time I had posted my code and had asked people to spot the errors (Do u remember "Can u spot the errors?") I was told it was better to recode than to spot the errors. The solution also had a sub "BetterToRecodeThanSpotting". So I didn't go through the trouble of pasting the code. I am sure even if someone tries to help me now he will again put up his own code rather than correct my existing code.

I have already created a sorting algorithm but I don't know why it's not running. there seems to be an error while passing the array as an argument from the
GetUniqueAndCount Sub to the Sort Sub. I would appreciate it if u could run the code and tell me where the error is.
 
Last edited:
Hi, joharmenezes!

Sometimes doing something from scratch is easier and it maybe for different reasons: clearness, more clever viewpoint, educational and guidelines, copying or extracting snippets from other sources, just to enumerate a few. It's a contributor's decision based on its own criteria, following or not the OP requirements; finally it's this last who'd thumb up or thumb down the suggested solution.

But when some being able to help read a question, in this case "how do I sort..." unless it's Harry Potter disguised, he has no chance to guess/suspect/deduce that there's an existing not fully working code at the other side of the cyberspace... because here all trust on people following the quoted rules of my previous post 1st paragraph.

So IMHO, yes, it's your fault. But it's not so serious, it's just a matter of fair play. If I were you I'd have included ALL the info that I could, i.e., the problem description, the existing code, and asked for advices that'd prioritize either updating or fixing your code (which doesn't guarantee nothing, since the contributor might find that possible o reasonable or not, and maybe he rewrites everything from scratch) or asking for a working code.

Regards!
 
joharmenezes,

once you have the array sorted, what's going on ? What's the purpose ?
'cause if it's to sort in the sheet, it's a wrong way, just need 4 code lines for that …

With a clear explaination …

________________________________________________________________
Question well formulated, solution guaranteed ! (Robert Durupt)
​
 
there is no need for a sample workbook
Click to expand...
I uploaded this workbook as the previous guy asked me to do it.
Click to expand...
Well not my fault
Click to expand...
(Do u remember "Can u spot the errors?")
Click to expand...

You seem to be going out of your way to insult people, you cannot even have the manners to refer to their name, the previous guy you are refering to goes by the name of Marc L.
Members have offered to help but you muddle everything and seem to want others to do all the work for you, when others point out what you need to do you actualy feel that you need to report them for daring to question your actions, or perhaps non actions would be a better discription.

Arrogance, ignorance and bad manners are the refuge of the foolish. You seem to be displaying all!

.
 
I don't know how I'd handle this, but I wonder if you could do something like:

for i = 1 to ubound(yoursourcearray)
holderarray(i) = application.large(yoursourcearray,i)
next i
 
admittedly hacky, but not bad for having not yet finished my cup of coffee:

Code: 
Sub sort()
Dim avSource() As Variant
Dim alHolder() As Long
Dim albuffer() As Long
Dim rSource As Range
Dim rOP As Range

Set rSource = Sheets("sheet1").UsedRange
avSource = rSource
ReDim alHolder(1 To UBound(avSource))
ReDim albuffer(1 To UBound(avSource))


For i = 1 To UBound(avSource)
  alHolder(i) = avSource(i, 1)
Next i

For i = 1 To UBound(alHolder)
  albuffer(i) = Application.WorksheetFunction.Large(alHolder, i)
  Debug.Print albuffer(i)
Next i

Set rOP = Range("f1").Resize(UBound(albuffer), 1)
rOP = Application.Transpose(albuffer)
rOP.NumberFormat = "m/d/yyyy"



End Sub
 
You must log in or register to reply here.
Back
Top