Cannot replace accidental column date-values

[cfp999]

Hi,


I have a simple Excel-sheet with some data from an SQL-export. Row B contains information on your typical age-intervals (0 - 9, 10 - 19, 20 - 29, 30 - 39....). The intervals are formatted as text. Unfortunately, Excel automatically converts the interval "10 - 19" to a date, Oct-19.


0 - 9
Oct-19
20 - 29
30 - 39
40 - 49
50 - 59
60 - 69
70- 79
80 - 89


When I examine these cells I notice that the original information is lost - formatting the cells back to "General" results in a date number = 43739.


I want to find and replace all instances of "Oct-19" in column B, with "'10 - 19" using VBA. This works perfectly well when done manually. I have recorded the steps, but for some reason, this does nothing when executed as code:

Columns("B:B").Select    Selection.Replace What:="01-10-2019", Replacement:="'10 - 19", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

I have tried replacing What:="01-10-2019" with "Oct-19". Also tried adding a format, What:=Format("01-10-2019", "dd/mm/yyyy"). Still nothing. I'm missing something here?

 

[mumps]

Try:

Sub test()
    Columns("B:B").Replace What:="10/19/2019", Replacement:="'10 - 19", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

 

[Fluff]

How about

What:=CDate("01-10-2019")

 

[cfp999]

Thank you, but I still get 'Oct-19'. May I should try forcing a general number format to the cells, and then identify them as 43739?

 

[ClaireS]

Just a suggestion as I haven't tried it out, but try formatting Column B (your age-bands column) as Text BEFORE running your code.

 

[mumps]

Have you tried the macro I suggested? It worked on the data that you posted.

 

[Fluff]

Thank you, but I still get 'Oct-19'. May I should try forcing a general number format to the cells, and then identify them as 43739?

Is that with the code mumps supplied, or the change I suggested?

 

[cfp999]

Hi, I tried both yours, and the code suggested by mumps. Since mumps got it to work, I think my problem may be related to the way my data is formatted. The age-intervals are "General", while the oct-19 is "Userdefined" mmm-yy. If I manually change the format of the column to "Text", the oct-19 entries changes to 43739. Then I am able to replace that number with 10 - 19:

Columns("B:B").Replace What:="43739", Replacement:="'10 - 19", LookAt:= _    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

However, for some reason, I cannot change the format of the column in code, only by doing it manually.