+ Reply to Thread
Results 1 to 2 of 2

Replace all MM in a column of dates

  1. #1
    Registered User
    Join Date
    02-03-2016
    Location
    Santa Ana, Ca
    MS-Off Ver
    2010
    Posts
    1

    Replace all MM in a column of dates

    HI there, Newbe. I need to use the replace funtion to change the month where ever it occures in the sheet. My dates are formated for mm/dd/yy. However the value of the dates are m/dd/yyyy as in the first nine months. This is a problem because I input from month to be 01/ and the to month to be 02/ and excel tells me no data exsist. If I input 1/ and to month as 02/ it works using the manual funtion on the sheet. My VB macro finds nothing. so what's wrong with this code:

    Sub DateChange()
    ' DateChange Macro
    ' Change the date & month
    ' Called from Ranges module11
    '
    Dim FromMo As String
    ' FromMo is the number of the old month and a / From B2 and changes all the dates
    ' ToMo is the number of the New month and a / is input here and changes all the dates
    Dim ToMo As String
    ' FromMo2 is from the last months range name month and is used to change all the range names
    ' ToMo2 is the new Month 3 char alfa month from C1 input in a prior module
    Dim FromMo1 As String
    Dim FromMo2 As String
    Dim ToMo2 As String
    FromMo = Range("B2")
    FromMo1 = Range("K45")
    FromMo2 = Left(FromMo1, 9)
    FromMo1 = Right(FromMo2, 3)
    FromMo2 = FromMo1
    ToMo2 = Range("C1")
    MsgBox ("DateChange! " & FromMo & "=from search " & "from=" & FromMo2 & " To=" & ToMo2)
    ToMo = InputBox("DateChange: Enter ToMo, the 2 char month and / together")
    Range("B1") = Left(ToMo, 2)
    If FromMo > " " Then
    ' numeric mo change
    ActiveWorkbook.ActiveSheet.Cells.Replace What:=FromMo, Replacement:=ToMo, LookAt:=xlPart, _ <--------- this does not work Cells are displayed as 01/12/16
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    ' Alfa month change
    ActiveWorkbook.ActiveSheet.Cells.Replace What:=FromMo2, Replacement:=ToMo2, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    End If
    End Sub

    Do I need to change the LookAt or the SearchFormat? maybe changing the data(dates) format to something other than mm/dd/yy. Why does the cell data display at the top in the edit bar display m/dd/yyyy when I click on a date like 01/12/16 . the leading 0 is missing.

    Thanks in advance.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: Replace all MM in a column of dates

    Probably nothing wrong with the code. Except for the fact that dates are numbers representing the number of days from the beginning of January 1900. Excel will interpret text that looks like a date and convert it into a number with a Date Format. Which is why you can manually change 01 to 02.

    But 01/01/2016 will be represented by the number 42370. Hence you won't necessarily find what you search for.

    Recording the manual actions gave me:

    Please Login or Register  to view this content.

    However, editing that and repeating it simply does not work.

    You could maybe store the dates in an array, loop through the array incrementing the month and then storing the dates back on the sheet.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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. [SOLVED] Enter dates in one column based on whether dates are consecutive in another column
    By ktbb0312 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-17-2015, 02:03 PM
  2. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  3. [SOLVED] Macros to find the dates in a column and replace with a different format. Excel 2003
    By Ashwin Sundarajan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-12-2012, 12:08 AM
  4. Excel Find and Replace for Dates
    By HP RodNuclear in forum Excel General
    Replies: 6
    Last Post: 12-20-2010, 11:08 AM
  5. Replace dates with text
    By brooksc29 in forum Excel General
    Replies: 1
    Last Post: 08-11-2010, 12:03 AM
  6. Find and replace dates
    By Paul Morgan in forum Excel General
    Replies: 2
    Last Post: 08-12-2007, 02:21 PM
  7. [SOLVED] How do I replace dates earlier than certain date?
    By Jose Martinez in forum Excel General
    Replies: 5
    Last Post: 04-29-2005, 05: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