Sorting Issue

[Pacman52]

Hi all,

So just to be up front my VBA knowledge is still basic - I'm learning as I go so please bear that in mind in any responses !

So, I have a worksheet called 'Contents'.
Col C from Row 7 down, shows all of the visible worksheet tab names.
All of the tabs are named automatically using the same criteria '01-07-2022 - sample text1', '11-12-2024 - sample text2' and so on basically they will all start with a 'date'

In the adjacent rows in Col B I have extracted the first 10 characters of the tab names in Col C for example using the sample tab names above B7 will show 01-07-2022 and B8 will show 11-12-2024.

What I'm trying to accomplish is to be able to 'convert' B7 ect into a date format and then sort the rows into oldest to newest but no matter what I try using the code below I can not get the values in Col B to be recognised as a date it seems to be seeing it as numbers and sorting in numerical order.

I did change the 'NumberFormat' to 'DateFormat' and tried using Cdate but it kept erroring with type mismatch. I have thought about referencing Col B values to another row to try and see if I could convert the values to a date that way.

Does anyone have any suggestions on how I could accomplish this as I'm all out of ideas

Sub ExtractDate()
Dim lastRow As Long
Dim i As Long

    Call UnprotectSheet

        lastRow = Cells(Rows.Count, "C").End(xlUp).Row

    For i = 7 To lastRow

        Cells(i, "B").Value = Left(Cells(i, "C").Value, 10)
        Cells(i, "B").NumberFormat = "mm-dd-yyyy"

    Next i

    Call ProtectSheet

End Sub

 

[Phuoc]

Try this:

Sub ExtractDate()
Dim lastRow As Long
Dim i As Long

    Call UnprotectSheet
        lastRow = Cells(Rows.Count, "C").End(xlUp).Row

    For i = 7 To lastRow
        Cells(i, "B").Value = ConvertDate(Left(Cells(i, "C").Value, 10))
        Cells(i, "B").NumberFormat = "mm-dd-yyyy"

    Next i
    Call ProtectSheet
End Sub
..........
Public Function ConvertDate(s As String) As Double
ConvertDate = DateSerial(Right(s, 4), Mid(s, 4, 2), Left(s, 2))
End Function

 

[Pacman52]

Hi Phuoc, thanks for the reply,

sadly its erroring out in the public function with the CovertDate saying its a type mismatch again.
I'm thinking it must be something in the way I'm writing the data to either Col C on the contents worksheet as logically it should work.

The 'flow' of the workbook is that a user clicks a button on the Menu worksheet, dependant on what button they click it opens the relevant user form, they then enter the date. That date is then written to a worksheet called 'Dates' into the relevant row in Col A and then concatenated with the adjacent row Col B (which has some text in it) with the value going into the adjacent row in Col C.

 =CONCATENATE(TEXT(A3, "dd-mm-yyyy"),"  -  ", B3)

It then copies a worksheet template to a new worksheet and renames it with the concatenated text (so using my example above a new worksheet is created and renamed '01-07-2022 - sample text1'). This worksheet name is then added to the contents worksheet into the next row so that a list of all the worksheets in the workbook is compiled and converted to a hyperlink.

All of this works perfectly but as there will be literally 100's of worksheets created eventually, it was picked up during testing that the users wanted to have the worksheet names sorted into date order so that the can easily find a worksheet and click the hyperlink to activate it.

 

[kvsrinivasamurthy]

Change this line

Cells(i, "B").Value = Left(Cells(i, "C").Value, 10)

as

Cells(i, "B").Value = Datevalue(Left(Cells(i, "C").Value, 10))

 

[Pacman52]

Thank you so much, that seems to have done the trick and it is not sorting it correctly. Hopefully I don't find anymore issues and can finally tick this project as completed !

 

[kvsrinivasamurthy]

Just add the code for sorting for the reference range after
Next i
Line.

 

[Pacman52]

Just add the code for sorting for the reference range after
Next i
Line.

Thanks I think the way I have written my subs and where I have called this it seems to have worked already but I'll bear that it mind if I do come up with any issues later on.

I've added about 20 odd worksheets with different dates as a test and so far it seem to be working.

Thanks again

 

[kvsrinivasamurthy]

Welcome.