Search & Replace macro
 

Hi,

I cant find this anywhere so please excuse possible repeat....

I would like a macro which will search for the value of a cell (in one
worksheet) and simply replace a value in another worksheet with the value of
another. E.G. Search for contents of B30 and replace with contents of F30.
Simple i hear you say, but my current macro always contains the actual
value at time of recording so if i change the value of the cells, the macro
searches for and replaces the values previously stored and not the new values
i'm using.
Here is what i have...
Sheets("Summary").Select
Range("B30").Select
Selection.Copy
Range("F30").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

I would like the values (nn) and 20 to be dynamic and always refer to what's
in the range("B30") and ("F30) rather than insert fixed values (taken at time
of recording).

Many thanks.

Search & Replace macro
 

try
with Sheets("Summary")
.Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beebolbod" wrote in message
...
Hi,

I cant find this anywhere so please excuse possible repeat....

I would like a macro which will search for the value of a cell (in one
worksheet) and simply replace a value in another worksheet with the value
of
another. E.G. Search for contents of B30 and replace with contents of F30.
Simple i hear you say, but my current macro always contains the actual
value at time of recording so if i change the value of the cells, the
macro
searches for and replaces the values previously stored and not the new
values
i'm using.
Here is what i have...
Sheets("Summary").Select
Range("B30").Select
Selection.Copy
Range("F30").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

I would like the values (nn) and 20 to be dynamic and always refer to
what's
in the range("B30") and ("F30) rather than insert fixed values (taken at
time
of recording).

Many thanks.

Search & Replace macro
 

WOW. nice one.

Thanks very much.

"Don Guillett" wrote:

try
with Sheets("Summary")
.Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beebolbod" wrote in message
...
Hi,

I cant find this anywhere so please excuse possible repeat....

I would like a macro which will search for the value of a cell (in one
worksheet) and simply replace a value in another worksheet with the value
of
another. E.G. Search for contents of B30 and replace with contents of F30.
Simple i hear you say, but my current macro always contains the actual
value at time of recording so if i change the value of the cells, the
macro
searches for and replaces the values previously stored and not the new
values
i'm using.
Here is what i have...
Sheets("Summary").Select
Range("B30").Select
Selection.Copy
Range("F30").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

I would like the values (nn) and 20 to be dynamic and always refer to
what's
in the range("B30") and ("F30) rather than insert fixed values (taken at
time
of recording).

Many thanks.

Search & Replace macro
 

How could i modify this to copy the variables from one worksheet and paste
into another please?

"Beebolbod" wrote:

WOW. nice one.

Thanks very much.

"Don Guillett" wrote:

try
with Sheets("Summary")
.Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beebolbod" wrote in message
...
Hi,

I cant find this anywhere so please excuse possible repeat....

I would like a macro which will search for the value of a cell (in one
worksheet) and simply replace a value in another worksheet with the value
of
another. E.G. Search for contents of B30 and replace with contents of F30.
Simple i hear you say, but my current macro always contains the actual
value at time of recording so if i change the value of the cells, the
macro
searches for and replaces the values previously stored and not the new
values
i'm using.
Here is what i have...
Sheets("Summary").Select
Range("B30").Select
Selection.Copy
Range("F30").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

I would like the values (nn) and 20 to be dynamic and always refer to
what's
in the range("B30") and ("F30) rather than insert fixed values (taken at
time
of recording).

Many thanks.

Search & Replace macro
 

Just reference them
..Cells.Replace sheets("othersheetname").range("b30"), .range("f30"), Loo
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beebolbod" wrote in message
...
How could i modify this to copy the variables from one worksheet and paste
into another please?

"Beebolbod" wrote:

WOW. nice one.

Thanks very much.

"Don Guillett" wrote:

try
with Sheets("Summary")
.Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beebolbod" wrote in message
...
Hi,

I cant find this anywhere so please excuse possible repeat....

I would like a macro which will search for the value of a cell (in
one
worksheet) and simply replace a value in another worksheet with the
value
of
another. E.G. Search for contents of B30 and replace with contents of
F30.
Simple i hear you say, but my current macro always contains the
actual
value at time of recording so if i change the value of the cells, the
macro
searches for and replaces the values previously stored and not the
new
values
i'm using.
Here is what i have...
Sheets("Summary").Select
Range("B30").Select
Selection.Copy
Range("F30").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

I would like the values (nn) and 20 to be dynamic and always refer to
what's
in the range("B30") and ("F30) rather than insert fixed values (taken
at
time
of recording).

Many thanks.

Search & Replace macro
 

Hi,

I tried that but it just inserted loads of values all over every sheet.
here's my code...
With Sheets("R11Variables")
.Cells.Replace Sheets("R11ScriptOutput").Range("c6"), .Range("a6"),
LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End With

I know it's probably bad coding but i'm using multiple with statements to
alter from c6 to c20 and a6 to a20. Doesn't affect it without the reference
to the sheet though, just thought i'd let you know in case it makes a
difference.

"Don Guillett" wrote:


Just reference them
..Cells.Replace sheets("othersheetname").range("b30"), .range("f30"), Loo
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beebolbod" wrote in message
...
How could i modify this to copy the variables from one worksheet and paste
into another please?

"Beebolbod" wrote:

WOW. nice one.

Thanks very much.

"Don Guillett" wrote:

try
with Sheets("Summary")
.Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beebolbod" wrote in message
...
Hi,

I cant find this anywhere so please excuse possible repeat....

I would like a macro which will search for the value of a cell (in
one
worksheet) and simply replace a value in another worksheet with the
value
of
another. E.G. Search for contents of B30 and replace with contents of
F30.
Simple i hear you say, but my current macro always contains the
actual
value at time of recording so if i change the value of the cells, the
macro
searches for and replaces the values previously stored and not the
new
values
i'm using.
Here is what i have...
Sheets("Summary").Select
Range("B30").Select
Selection.Copy
Range("F30").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

I would like the values (nn) and 20 to be dynamic and always refer to
what's
in the range("B30") and ("F30) rather than insert fixed values (taken
at
time
of recording).

Many thanks.