What's wrong here?
 

The code below inserts loads of incorrect values all over the place, rather
than in the hoped for areas. any ideas? I'm trying to search and replace
values typed into cells which reference another sheet. E.G. replace contents
of variable1 with say contents of C1 (typed in sheet 1) but also replace
variable 1 found on say sheet 3.
Here's what i have...

With Sheets("Sheet1")
.Cells.Replace Sheets("Sheet2").Range("c6"), .Range("a6"),
LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End With

many thanks

Rob

What's wrong here?
 

If you are getting replacements "all over the place", I would suspect the
parameter LookAt:=xlPart is allowing substring matches in the other cells.
What happens if you change that parameter to LookAt:=xlWhole?

Rick


"Beebolbod" wrote in message
...
The code below inserts loads of incorrect values all over the place,
rather
than in the hoped for areas. any ideas? I'm trying to search and replace
values typed into cells which reference another sheet. E.G. replace
contents
of variable1 with say contents of C1 (typed in sheet 1) but also replace
variable 1 found on say sheet 3.
Here's what i have...

With Sheets("Sheet1")
.Cells.Replace Sheets("Sheet2").Range("c6"), .Range("a6"),
LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End With

many thanks

Rob

What's wrong here?
 

hI,

That didn't solve it. I get a value (one of my intended variable values)
inserted in every cell in almost every column in many worksheets.
I only want my search and replace to reference a specific worksheet.

Thanks

Rob

"Rick Rothstein (MVP - VB)" wrote:

If you are getting replacements "all over the place", I would suspect the
parameter LookAt:=xlPart is allowing substring matches in the other cells.
What happens if you change that parameter to LookAt:=xlWhole?

Rick


"Beebolbod" wrote in message
...
The code below inserts loads of incorrect values all over the place,
rather
than in the hoped for areas. any ideas? I'm trying to search and replace
values typed into cells which reference another sheet. E.G. replace
contents
of variable1 with say contents of C1 (typed in sheet 1) but also replace
variable 1 found on say sheet 3.
Here's what i have...

With Sheets("Sheet1")
.Cells.Replace Sheets("Sheet2").Range("c6"), .Range("a6"),
LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End With

many thanks

Rob