# Union Method -how to best use it

1. ## Union Method -how to best use it

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

2. ## Re: Union Method -how to best use it

``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)