Removing a // double slash in a date

[ou812]

Hi,

Back before my time, a program that was written that we no longer have access to the source code wrote dates for some cells with a double slash such as 12//01/2018. I have to go through multiple worksheets in the workbook and manually replace these after the application completes its run. There are a total of 7 tabs and the date appears in different columns on different tabs. For example, on tab "Tampa", the date column is D while on tab "Miami" the date column is F. Is there a way in VBA to seek out the column (or date type cell) and if the // exists, to remove the one slash creating an actual date?

 

[Logit]

HOME tab / EDITING pane / Find & Select .... then Replace

 

[ou812]

That is how I manually fix this now, but I need to setup automation scripts to complete this job without a human present and therefore believe VBA code would be needed.

 

[Logit]

.

Option Explicit


Sub FindReplaceAll_CountReplacements()
'PURPOSE: Find & Replace text/values throughout entire workbook, notify user of how many cells were affected
'SOURCE: www.TheSpreadsheetGuru.com


Dim sht As Worksheet
'Dim fnd As Variant
'Dim rplc As Variant
Dim ReplaceCount As Long


Dim fnd, rplc As String


fnd = InputBox("Enter OLD term. ", "Old Term ?")
rplc = InputBox("Enter NEW term. ", "New Term ?")


For Each sht In ActiveWorkbook.Worksheets


  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")


  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
Next sht


MsgBox "I have completed my search and made replacements in " & ReplaceCount & " cell(s)."


End Sub

 

[ou812]

The problem with this code is I need to know the exact date with the // in it. One cell might have 12/1/2018, the next row down might have 7//21/2018, and the next might have 3//18/2018. This script would take human intervention to set the search condition of an entire string, and then what the replacement needs to be. I need a solution that is going to take the string in as an array, find to index positions that are one position apart that are both contain a /, and create a new string that removes one of the slashes, all without human intervention.

It is a cool script though and I will put it aside for a future reference though.

 

[Logit]

.
The dates I entered here were done manually. (It works here)

Are your dates created by formulas ?

 

[ou812]

The code you provided is not much different than me sitting at the keyboard each week and performing a Find & Select and Replace. The problem with this code is there won't be anyone sitting in front of a monitor to lookup bad dates and them along with the correct dates. This is an auto run program with no person to enter dates. The program needs to be intelligent enough to look at all dates and determine if the date contains a // or is properly formatted and therefore to ignore. The spreadsheet is being created every week automatically from a database. There was a coding error in the program that creates the dates in the spreadsheet. As I said in the original post, we do not have access to the original source code for the program to correct the error at the source and therefore have to fix the errors after the spreadsheet is created. I currently have to do this manually each week with search and replace but as a human, I can read the various dates and enter the values into the search box. I can no longer afford the time to do this and have to automate the process so a solution that has me inputting any values will not work. I therefore need a completely 100% automated coded solution.

 

[MARK858]

Why does it need to look for dates? do you have any other cells that have // in them that aren't dates?

As for 100% automated you need to tell us what is to trigger the macro to run.

 

[ou812]

I was able to come up with the code to fix this error. Here is a snippet:

    Range("C4:C" & newlastrow).Select
    Selection.Replace What:="//", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

I have 2 more dates that need repairing. The first is 121/2018 which should be reading 12/1/2018.


The second is 11/12018 which should be 11/1/2018.

I am unsure how to build the logic to spot these and repair.

 

[MARK858]

If your dates with // are in multiple columns and on multiple sheets all you should need is

Sub Removediag()
    Dim sht As Worksheet
    For Each sht In ActiveWorkbook.Worksheets
        sht.UsedRange.Replace "//", "/", xlPart
    Next
End Sub

If there aren't cells that have // and aren't dates

As for your other queries is there any logic as to why 11/12018 should be 11/1/2018 rather than 1/11/2018?