Find and Replace with contents of cell

[Ron]

I have a range of cells, A5:C42, with multiple instances in the format
00-00-00.

Cell A4 has the date in the format 06-01-10.

I want to use a quick macro attacked to a picture that replaces the 00-00-00
with the 06-01-10 in all instances.

A hard coded:
Range("A5:C42").Select
Selection.Replace What:="00-00-00", Replacement:="06-01-10", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

works fine, but I do this daily and monthly and don't want to edit the macro
each day. I need the Replacement: equal to cell A4.

Thanks,
Ron

 

[Don Guillett]

Try this
Sub replace00_SAS()
With Range("a5:C42")
.Replace "00-00-00", Range("a4"), _
SearchOrder:=xlByRows
.NumberFormat = "mm-dd-yy"
End With
End Sub

 

[Dave Peterson]

If you wanted to use today's date, you could drop the A4 entry:

Dim myDateStr as string
mydatestr = format(date,"mm-dd-yy")
selection.replace what:="00-00-00", replacement:=mydatestr, ...

But if you have a real date in A4 of the activesheet and want to use that date:

mydatestr = format(activesheet.range("a4").value,"mm-dd-yy")
selection.replace what:="00-00-00", replacement:=mydatestr, ...

 

[ryguy7272]

Range("A5:C42").Select
Selection.Replace What:="00-00-00", Replacement:=Range("A4"), LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

 

[RonTheOldGuy]

Thanks, that's what I was looking for. I couldn't figure out the format.

Actually, all the answer will work, but this one is a better fit.

Thanks to all
Ron