Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy contents of Find (Find and Replace) | Excel Discussion (Misc queries) | |||
Find & Replace part of cell contents | Excel Programming | |||
Replace #VALUE! with contents of cell?? | Excel Worksheet Functions | |||
Find and Replace Hyperlink Contents | Excel Discussion (Misc queries) | |||
replace cell contents | Excel Worksheet Functions |