macro for find and replace but prob with variable

[blackduck]

The value(text id) in cells E13:AC13 can change so I want the macro to pick up whatever is in the cell and put it as the replacement value.

The macro I have recorded is as follows:
Sub pleasework()
' pleasework Macro
Range("E13").Select
Selection.Copy
Range("E16:E68").Select
Selection.Replace What:="Site***.xls", Replacement:="Site018.xls", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
Range("F13").Select
Selection.Copy
Range("F16:F68").Select
Selection.Replace What:="Site***.xls", Replacement:="Site019.xls", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
Range("G13").Select
Selection.Copy
Range("G16:G68").Select
Selection.Replace What:="Site***.xls", Replacement:="Site027.xls", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Have been trying to work around this for ages. Could anyone please help with this?

 

[xlbo]

dim strRplace as string

strReplace = range("A1").text

Selection.Replace What:="Site***.xls", Replacement:=[b]strReplace[/b], LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blackduck]

Thanks xlbo,
I have replaced my code with your suggestion but get run time error '1004' application-defined or object-defined error.

I put a helper row in so eg E74 ="Site"&E13&".xls".

My code is as:
Sub pleasework()
'
' pleasework Macro
'
Dim strReplace As String

strReplace = Range("E74").Text
Range("E16:E68").Select
Selection.Replace What:="Site***.xls", Replacement:=strReplace, LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
strReplace = Range("F74").Text
Range("F16:F68").Select
Selection.Replace What:="Site***.xls", Replacement:=strReplace, LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
strReplace = Range("G74").Text
Range("G16:G68").Select
Selection.Replace What:="Site***.xls", Replacement:=strReplace, LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Any ideas of what I have done wrong?
thanks
k

 

[xlbo]

what is in E13 ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question