Search Date in Worksheet and Set Format

[Code2Code]

Hi All,

I have done several times a macro, but however the Search Macro won't work.

So I searched a little bit and did some modification, however it's still not working.


Here is the idea, I am looking for Cells which have a Date Format mostly have a Date Format d/m/yy or m/d/yy or similar.

However, I want it to format them all in dd/MMM/yyyy
Once I click on the Marco it should process all Dates in the right format.

My code is as follows:

Sub macro1()

Dim ws As Worksheet
Dim rCell As Range
Dim lReply As Long

Application.FindFormat.NumberFormat = "m/d/yyyy"
Application.ReplaceFormat.NumberFormat = "[$-409]d-mmm-yyyy;@"


For Each ws In Sheets
Set rCell = ws.Cells.Find(What:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=True)

Next ws

If rCell Is Nothing Then

lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)

If lReply = vbYes Then Run "macro1()":

End If



End Sub

But it won't run smoothly, maybe I have overlooked something.

Please help.

[Kenneth Hobs]

A simply recorded macro would be:
Application.FindFormat.NumberFormat = "m/d/yyyy"
Application.ReplaceFormat.NumberFormat = "[$-409]d-mmm-yyyy;@"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

You may want to change the "m/d/yyyy" with "m/d/yyyy;@".

[Code2Code]

Still not working, I have still Runtime- Error "1004"

It hangs/stops at

Application.FindFormat.NumberFormat = "m/d/yyyy"

also tried

Application.FindFormat.NumberFormat = "m/d/yyyy;@"


Please help, as it seems a common issue.
Recording a Macro is simple, but then running again, won't work, so there is manual intervention needed somehow.

[Kenneth Hobs]

I don't see that error. If you don't use a proper number format, that can be an issue. Try setting your from format in A1 and your to format in A2 manually. When you run this code, it will show actual formats in VBE's Immediate window. Use those number formats.
Sub t()
Debug.Print Range("A1").NumberFormat
Debug.Print Range("A2").NumberFormat
Application.ReplaceFormat.NumberFormat = "[$-409]d-mmm-yy;@"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

[Code2Code]

Hi Kenneth,

yes, the format seems right.
It print out the same format, however when I am doing on the whole worksheet, it seems not working. Also it does not really work at all here.

I tried also this one as recorded with Macro:

Sub macro1()

Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "m/d/yyyy"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
With Application.FindFormat.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Application.ReplaceFormat.NumberFormat = "[$-409]d-mmm-yyyy;@"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

End Sub


Then it hangs on:
Application.ReplaceFormat.NumberFormat = "[$-409]d-mmm-yyyy;@"

with Error 1004, don't know why.

[Kenneth Hobs]

I doubt that the Immediate window showed a format of "m/d/yyyy". It should be "m/d/yyyy;@". Your code then worked for me in 2010.

[Code2Code]

This is my immediate window showing:


m/d/yyyy
[$-409]d-mmm-yyyy;@


It seems that some cells are not compatible to the format.

[Kenneth Hobs]

Post an example workbook so that we can better help you.