CODEWITHSUNDEEP
vbaexcel
user3333321
user3333321

Reputation: 23

How can I use function DATEVALUE in a code vba

How can I use function DATEVALUE in a VBA code, in order to convert a cell from text to Date.

Ex. I have E1= "29/10/2014"

EXCEL recognizes it as text. If I double click on the cell it reverses it to date correctly.

I want to run a macro and convert all this values to Date. I must say that I have an excel sheet, Range A1:BJ10223 and columns with dates are the E,H,S,V,AB,AF,AJ,AL,AO,AS,AY,BE,BH. Not all dates are reflecting as text values. Some are ok.

Upvotes: 0

Views: 1109

Answers (2)

user4039065
user4039065

Reputation:

The most expedient method I'm aware of to convert pseudo-Text-Dates to actual date values is through the Data ► Data Tools ► Text-to-Columns command which can be looped through in VBA.

Sub TXT2DMY()
    Dim v As Long, vCOLs As Variant
    vCOLs = Array("E", "H", "S", "V", "AB", "AF", "AJ", "AL", "AO", "AS", "AY", "BE", "BH")
    With ActiveSheet
        For v = LBound(vCOLs) To UBound(vCOLs)
            If CBool(Application.CountA(.Columns(vCOLs(v)))) Then _
                .Columns(vCOLs(v)).TextToColumns Destination:=.Columns(vCOLs(v)), _
                  DataType:=xlFixedWidth, FieldInfo:=Array(0, 4)
        Next v
    End With
End Sub

The difference may not be appreciable on smaller worksheets but anything over 500 rows of data with that many columns will definitely be faster.

Upvotes: 1

Bathsheba
Bathsheba

Reputation: 234635

The equivalent function in VBA is CDate which converts its argument (formally a Variant) to a Date type.

Upvotes: 0

Related Questions