Find and Replace with contents of cell
 

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

Find and Replace with contents of cell
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
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

Find and Replace with contents of cell
 

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, ...

Ron wrote:

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

--

Dave Peterson

Find and Replace with contents of cell
 

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Ron" wrote:

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

Find and Replace with contents of cell
 

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

"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Ron" wrote:

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