+ Reply to Thread
Results 1 to 2 of 2

Union Method -how to best use it

  1. #1
    Registered User
    Join Date
    10-19-2016
    Location
    Hartford, Connecticut
    MS-Off Ver
    excel 2010
    Posts
    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. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,570

    Re: Union Method -how to best use it

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. method union of object _global failed
    By sanju2323 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-09-2015, 05:47 AM
  2. [SOLVED] problem with union method in data filter
    By edopts in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-11-2014, 12:46 PM
  3. Help me to print a range before the other in a Union method.
    By OFUENT13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2012, 05:03 PM
  4. Setting Ranges With Union Method
    By Preston900 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2008, 09:17 AM
  5. [SOLVED] Union Method
    By Noah in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2006, 10:45 AM
  6. [SOLVED] RE: Union Method
    By Gary''s Student in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2006, 07:40 AM
  7. [SOLVED] Union method for Range Object
    By Chad in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-10-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1