VBA Convert Date Macro

[SBF12345]

Greetings,

I have a macro that I would like to use to convert date values in columns of data. I would like to be able to set the column value to convert with the integer variable 'a'.

The material inside the with statement is not functioning. I have two questions.

1)How do I set the output style of the DateValue function to 'MM/DD/YYYY'?
2)How is best to loop through the column with values to convert? I would like to convert them so that the output is placed in the same cell as the input contents. Ideas?

Thanks,

Sub SAVE_MACRO_Date_Convert()

Dim a As Integer
Dim b As Integer
Dim c As Long
Dim Var1 As Variant


'column value to convert
a = 4


b = Cells(Rows.Count, a).End(xlUp).Row


With ActiveSheet
            
            For c = 2 To b


                Var1 = DateValue(CStr(.Cells(c, a))) ' not sure how to output values in the same location as the input source


            Next c
            
            '.NumberFormat = "mm/dd/yyyy"
        
End With


End Sub

 

[njimack]

Untested...

Sub SAVE_MACRO_Date_Convert()
Dim Col As Long, LastRow As Long
Dim cl As Range


'column value to convert
Col = 4

LastRow = Cells(Rows.Count, Col).End(xlUp).Row

For Each cl In Range(Cells(2, Col), Cells(LastRow, Col))
    cl = DateValue(CStr(cl.Value))
Next cl
     
Range(Cells(2, Col), Cells(LastRow, Col)).NumberFormat = "mm/dd/yyyy"

End Sub

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub SAVE_MACRO_Date_Convert()
  Dim A As Long
  
  'Column to convert
  A = 4
  
  Columns(A).TextToColumns Cells(1, A), xlDelimited, , , False, False, False, False, False, FieldInfo:=Array(1, 3)
  Columns(A).SpecialCells(xlConstants).NumberFormat = "mm/dd/yyyy"
End Sub[/td]
[/tr]
[/table]

Edit Note: Personally, I would use more meaningful variable names so that when you look at your code (possibly to modify it) in the future, it will be easier to review. For example, instead of calling the variable that designates the column number A, I would use either Col or (a little more self documenting) ColNum.

 

[SBF12345]

nji, the code seems to be running fine. Although I have date values that are of the year '1866' and am receiving an error on the line:

[COLOR=#333333] cl = DateValue(CStr(cl.Value))[/COLOR]

Are there limits to the dates that are recognized with this method?

 

[SBF12345]

Rick, I tried running your code. It seems to leave the sheet unchanged as it is. I'll have a look at it again to try to understand the component parts.

 

[MARK858]

nji, the code seems to be running fine. Although I have date values that are of the year '1866' and am receiving an error on the line:

Are there limits to the dates that are recognized with this method?

Excel only recognises dates from 1900.

 

[SBF12345]

gotcha