My question (A) --With the current program below on the Sub Convert Dates; how do I dynamically program the current parameters (one column) by expanding it into 5 separate non touching columns? (For an example, instead of column Q below; make it Q,R, V, W, Y).
From a programming standpoint I have other questions too.
Question B- To expand the range of data being converted from one column to multiple non-touching columns should the looping method change ?
Question C- If the looping method doesn't change for dynamically programming different methods does the Inclusion of calling a function change the programming style.
Question D- if the Looping method does change- what other factors do you consider for using a function below too
Col Q Col R Col V Col W Col Y
Date 1 Date 3 Date 5 Date 3 Date 3
41983 41983 41983 41983 41983
42318 42318 42318 42318 42318
47381 47381 47381 47381 47381
47381 47381 47381 47381 47381
47381 47381 47381 47381 47381
Sub ConvertDates()
'changes formats of dates want to do it multiple positions'
Dim Cell As Range
Dim lastrow As Long
lastrow = Range("Y" & Rows.Count).End(xlUp).Row
For Each Cell In Range("Y2:Y" & lastrow)
If InStr(Cell.Value, ".") <> 0 Then
Cell.Value = RegexREplace(Cell.Value, _
"(\d{2})\ .(\d{2})\.(\d{4})", "$3-$2-$1")
End If
If InStr(Cell.Value, ".") <> 0 Then
Cell.Value = RegexREplace(Cell.Value, _
"(\d{2})\ .(\d{2})\.(\d{4})", "$3-$2-$1")
End If
Cell.NumberFormat = "yyyy--mm-d;@"
Next
End Sub
Function RegexREplace(ByVal text As String, _
ByVal replace_What As String, _
ByVal replace_with As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = replace_What
REGlobal = True
RegexREplace = RE.Replace(text, replace_with)
End Function
Bookmarks