Find and Replace Macro

[MrMiyaghi]

Hi all

I have a column that will have a date in multiple cells and every day the date will change. The format of the date is dd/mm/yyyy and I'm trying to create a macro that will select all of the cells and replace the date with 'dd/mm/yyyy (whatever the date is).

Does anybody know the best way to do this?

Thanks in advance

 

[alansidman]

Do you wish all the cells with dates to be changed to the current date?

 

[MrMiyaghi]

Hi Alan

Thanks for the reply.

No I'd like whatever date is in the cell to change.

So if the date in the cell is 21/12/2017 I'd like it to change to '21/12/2017 and if the date in the cell is 22/12/2017 I'd like it to change it to '22/12/2017.

Hopefully that makes more sense

 

[MrMiyaghi]

Just to clarify as it might not be that clear.

Whatever date is in the cell I would like to replace it with the same date but with an ' in front of the date

 

[alansidman]

Option Explicit


Sub convertDates()
    Dim rng As Range
    Dim c As Range
    Set rng = Range("A1").CurrentRegion
    For Each c In rng
        If IsDate(c) Then
            c.NumberFormat = "@"
            c = Format(c, "mm/dd/yyyy") 'change this format to what is correct for your region
        End If
    Next c
End Sub