Help With VBA Code

[purceld2]

Hi Guys

I have recorded some VBA code to convert dates into their number format. The code works with the first Column but fails while doing the second Column.

I discovered that the reason was that the Column F had blank cells and for some reasons it does not like that.

Can somebody help me with this please

Sub Desp()
'
' Desp Macro
'
    Range("E3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("E3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Range("F3").Select
  
    Range("F3").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Selection.TextToColumns Destination:=Range("F3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
      


    Selection.NumberFormat = "m/d/yyyy"
End Sub

Perry Hill Territory Spreadsheet.xlsm
	E	F
2	Assigned	Returned
3	14/05/2016	25/06/2016
4	08/05/2016	19/08/2016
5	11/07/2016	26/12/2016
6	03/08/2017	26/03/2017
7	26/03/2017	07/05/2017
8	29/07/2017	15/10/2017
9	02/10/2018
10	21/07/2018	28/07/2018
11	11/01/2018	03/01/2019
12	19/01/2019	25/02/2019
13	04/03/2019	18/04/2019
14	28/06/2019	28/07/2019
15	09/07/2019	18/09/2019
16	12/12/2019	04/01/2020
17	26/09/2020	06/12/2020
18	19/09/2021
19	22/04/2016	08/07/2016
20	08/06/2016	14/08/2016
21	10/03/2016	03/12/2016
22	21/01/2017	15/03/2017
23	29/03/2017	02/04/2017
24	05/12/2017	18/06/2017
25	22/07/2017	30/07/2017
26	09/02/2017	06/10/2017
27	12/06/2017	15/01/2018
28	16/05/2018	25/05/2018
29	28/07/2018	04/08/2018
30	13/10/2018	25/11/2018
31	02/09/2019	20/02/2019
32	23/03/2019	15/04/2019
33	20/06/2019	24/07/2019
34	14/08/2019	15/08/2019
35	10/04/2019	06/10/2019
36	12/11/2019	23/12/2019
37	03/07/2020	13/03/2020
Sheet1

 

[Zot]

You can select the whole range just like this
Range("E3", Cells(Rows.Count, "F").End(xlUp)).Select

If just column F
Range("F3", Cells(Rows.Count, "F").End(xlUp)).Select

 

[johnnyL]

I am confused as to what the goal is here. Is the goal to convert a 5 digit serial date from the column E into a regular date and place result into the cell to the right of that, column F?

If so try the following:

Sub DespV2()
'
    Dim Cell            As Range
    Dim ColumnOffset    As Long
'
    ColumnOffset = 1
'
    For Each Cell In Range("E3:E" & Range("E" & Rows.Count).End(xlUp).Row)
        Cell.Offset(0, ColumnOffset) = Format(Cell, "m/d/yyyy")
    Next
End Sub

If that is not the goal, perhaps submit a before and after display of data. Some further word descriptions might be in order also.

 

[purceld2]

I am confused as to what the goal is here. Is the goal to convert a 5 digit serial date from the column E into a regular date and place result into the cell to the right of that, column F?

If so try the following:

Sub DespV2()
'
    Dim Cell            As Range
    Dim ColumnOffset    As Long
'
    ColumnOffset = 1
'
    For Each Cell In Range("E3:E" & Range("E" & Rows.Count).End(xlUp).Row)
        Cell.Offset(0, ColumnOffset) = Format(Cell, "m/d/yyyy")
    Next
End Sub

If that is not the goal, perhaps submit a before and after display of data. Some further word descriptions might be in order also.

Sorry if I confused you, I am a bit confused myself. The object is to change i.e. "16/11/2021" to a 5 digit serial date.
I tried formatting it but it just don't work. Hope this helps.

 

[johnnyL]

If they are actual dates, you can just format the cells with the dates to 'General'

If they are text dates, you could convert them to actual dates first. It appears you only have two of these toward the bottom of the column F.

 

[Zot]

Sorry if I confused you, I am a bit confused myself. The object is to change i.e. "16/11/2021" to a 5 digit serial date.
I tried formatting it but it just don't work. Hope this helps.

Working with date always give me headache ? Excel has nasty habit of being stubborn. It will switch back to System Date format which your Excel follow.

Try this and you will see that nothing changed

Sub Desp1()

Range("E3", Cells(Rows.Count, "F").End(xlUp)).Select
Selection.NumberFormat = "m/d/yyyy"

End Sub

but if you make slight change to format, it will work:
Selection.NumberFormat = "mm/dd/yyyy"
Selection.NumberFormat = "m-dd-yyyy"

It seems to me that slash is taken as general date which follow the System Date

That is when you write code more like human action, using Selection. If the code is more like computation or pure VBA action, then the conclusion is different.

The code below will not change anything even though format "mm/dd/yyyy" works using Selection. Using "mm-dd-yyyy" would not work either. It will take Date number in Dt and write into cell to follow system default.

Sub Test()

Dim cell As Range, rng As Range
Dim Dt As Date

Set rng = Range("E3", Cells(Rows.Count, "F").End(xlUp))

For Each cell In rng
    If Not IsEmpty(cell) Then
        Dt = cell
        cell = Format(Dt, "mm/dd/yyyy")
    End If
Next

End Sub

If you use other character instead if Slash / or Dash -, then it will convert, but it will become strange because it is unfamiliar form to human
I tried to add space at the end but it will not work either like
cell = Format(Dt, "mm-dd-yyyy" & " ")

If use some other non-print character, then it will work
cell = Format(Dt, "mm-dd-yyyy" & Chr(160))

This is my observation. I hope this helps.

So, when I import sheets with with UK and US date format, read it and copy as Text. Then convert to date serial to be used in code.

 

[Alex Blakenburg]

Working with date always give me headache ?

I am with you on that.
The "m/d/yyyy" not doing anything was interesting.

I don't think your comparision between Selection and Cell is valid though.

With Selection you are using Selection.NumberFormat = "mm/dd/yyyy"
If you change you cell code to be Cell.NumberFormat = "mm/dd/yyyy", it will produce the same result.
To use Format and get the expected result you would need to change the Cell format to Text first
ie precede the format line with Cell.NumberFormat = "@"

 

[Zot]

I am with you on that.
The "m/d/yyyy" not doing anything was interesting.

I don't think your comparision between Selection and Cell is valid though.

With Selection you are using Selection.NumberFormat = "mm/dd/yyyy"
If you change you cell code to be Cell.NumberFormat = "mm/dd/yyyy", it will produce the same result.
To use Format and get the expected result you would need to change the Cell format to Text first
ie precede the format line with Cell.NumberFormat = "@"

Yes. Change to Text format using @. That is what I did for display purpose ?. Then read as Date value to be used in calculation.