Help With VBA Code

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
581
Office Version
  1. 2013
Platform
  1. Windows
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

VBA Code:
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
EF
2AssignedReturned
314/05/201625/06/2016
408/05/201619/08/2016
511/07/201626/12/2016
603/08/201726/03/2017
726/03/201707/05/2017
829/07/201715/10/2017
902/10/2018
1021/07/201828/07/2018
1111/01/201803/01/2019
1219/01/201925/02/2019
1304/03/201918/04/2019
1428/06/201928/07/2019
1509/07/201918/09/2019
1612/12/201904/01/2020
1726/09/202006/12/2020
1819/09/2021
1922/04/201608/07/2016
2008/06/201614/08/2016
2110/03/201603/12/2016
2221/01/201715/03/2017
2329/03/201702/04/2017
2405/12/201718/06/2017
2522/07/201730/07/2017
2609/02/201706/10/2017
2712/06/201715/01/2018
2816/05/201825/05/2018
2928/07/201804/08/2018
3013/10/201825/11/2018
3102/09/201920/02/2019
3223/03/201915/04/2019
3320/06/201924/07/2019
3414/08/201915/08/2019
3510/04/201906/10/2019
3612/11/201923/12/2019
3703/07/202013/03/2020
Sheet1
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
2,901
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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:

VBA Code:
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

Well-known Member
Joined
Aug 18, 2005
Messages
581
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
2,901
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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.
VBA Code:
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 :ROFLMAO:
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.
 
Last edited:

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,655
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,311
Messages
5,836,583
Members
430,438
Latest member
David Gr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top