Prev: Arrays of Controls
Next: Quiz test
From: Ron on 4 Jun 2010 16:56
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
From: Don Guillett on 4 Jun 2010 18:37
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
dguillett(a)gmail.com
"Ron" <Ron(a)discussions.microsoft.com> wrote in message
news:7D4C320A-6EED-4981-A2BF-1C068BF4BEC4(a)microsoft.com...
>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

From: Dave Peterson on 4 Jun 2010 18:38
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
From: ryguy7272 on 4 Jun 2010 18:43
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
From: RonTheOldGuy on 4 Jun 2010 21:17
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
 | 
Pages: 1
Prev: Arrays of Controls
Next: Quiz test