Search code examples
exceldatereplacefindvba

VBA - Find date within text containing format 'DDMMYYYY'

I've scanned and found many similar answers to my question although none of them are quite ticking the boxes, so I'll explain what I'm after and hopefully it makes sense:

  • I have imported data into an excel spreadsheet (working with 2007) from a TXT, which contains various dates (all in the format DDMMYYYY).
  • What I'm trying to do is create a sub routine which finds any dates in this format and subsequently decreases the date by 1 year.
  • The dates are within a range when the TXT has been imported (in this instance, the first 2 dates appear under range A6) so ideally I would like to specify that range because I don't necessarily want to decrease all dates present in the TXT.

For example, here's the incorrect code which I am sure needs some serious tweaking!

    Cells.Replace What:="Dates", Replacement:="Dates-1", LookAt:=xlPart,    _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=DDMMYYYY, _
    ReplaceFormat:=False

I gather there is some prior definition that needs to be carried out before anything like the above code would work but if there is anyone who can help, I'd appreciate it!

Many thanks,

Robin.

Solution

  • I do not believe any tweak will allow your code to achieve the effect you seek. I cannot give a definitive answer because I do not quite believe your description. I hope the information below will allow you to investigate your data and develop an appropriate solution.

    You are not, as far as I know, using SearchFormat correctly. You need something like this:

    With Application.FindFormat
  .Font.Bold = True
End With

Set Rng = .Find(. . .  SearchFormat = True  . . .)

    The VBA Editor’s Help for Find states: “SearchFormat Optional Variant. The search format.” but gives no example to show what that means. I cannot find an example of using the find format facility that is not in the above style. That is: you use FindFormat to specify the format(s) of interest and set SearchFormat to True to indicate that those formats are to be searched for.

    You could try:

    With Application.FindFormat
  .NumberFormat = "DDMMYYYY"
End With

    I have not tried this and I cannot find any documentation that explains what format types can be searched for. If this works, it will almost certainly be faster and easier than anything based on the following information.

    For Excel to import a string within a text file as a Date, it must:

    • Recognise the string as a date.
    • Convert that string to a number.
    • Store that number with a number format that indicates it is a date.

    Excel stores dates as the number of days since 1/1/1900. For example, “21 August 2015” will be stored as 42238 with a number format of “dd mmmm yyyy”. There is nothing about the cell value that says it’s a date. You can enter the cell value as 42238 and later set the number format to “dd mmm yy” and the value will display as “21 Aug 15”.

    Your description implies the dates are held in the text file as eight-digit values which Excel recognises as dates and therefore sets the number format to "DDMMYYYY". I have never managed to get Excel to recognise eight-digit values as dates. If you have succeeded, I would like to know how.

    My best suggestion is:

    For Each CellCrnt in RngImport
  If CellCrnt.NumberFormat = "ddmmyyy" Then
    ' Add year to date
  End If
Next

    Extension

    If you perform the actions I requested in the comment against your question, we should be able to identify the type of the values you wish to locate and modify. This extension explains how you might use that information.

    In my earlier code I used RngImport to represent the Range of the imported data. One of your remarks makes me wonder: do you know how to initialise that Range?

    Do we have to search the entire Range for these “dates”? That is, are there scattered across the data or are they restricted to a single column? The final code will be faster if we do not have to examine every cell.

    My guess is we will need something like:

    If IsDate(Mid(CellValue,1,2) & "/" & Mid(CellValue,3,2) & "/" & Mid(CellValue,5,4)) Then

    to convert “ddmmyyyy” to “dd/mm/yyyy” and then test the new string to be a date.