Removing a // double slash in a date

ou812

New Member
Joined
Aug 24, 2018
Messages
19
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,577
HOME tab / EDITING pane / Find & Select .... then Replace
 
Last edited:

ou812

New Member
Joined
Aug 24, 2018
Messages
19
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

Well-known Member
Joined
Aug 31, 2016
Messages
4,577
.
Code:
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

New Member
Joined
Aug 24, 2018
Messages
19
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.
 
Last edited:

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,577
.
The dates I entered here were done manually. (It works here)

Are your dates created by formulas ?
 

ou812

New Member
Joined
Aug 24, 2018
Messages
19
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,688
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 
Last edited:

ou812

New Member
Joined
Aug 24, 2018
Messages
19
I was able to come up with the code to fix this error. Here is a snippet:

Code:
    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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,688
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
If your dates with // are in multiple columns and on multiple sheets all you should need is
Code:
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?
 

Forum statistics

Threads
1,181,759
Messages
5,931,887
Members
436,805
Latest member
waseem abbas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top