VBA Search & Replace Within Range

[srdavisgb]

I am attempting to search and replace a value within a specific range in a worksheet. I do not want to replace the value in other places in the worksheet. I've tried the following two vba options. In both cases the code is replacing the values in columns J & N with "1A". So, when the code attempts to find "1: " in column N, it cannot be found.

How can I search and replace "1: " only within the range specified (e.g., "J2:J" & lastrow)?

I do appreciate any suggestions.

Option #1

Dim r1 As Range
Set r1 = Range("J2:J" & lastrow)
r1.Replace What:="1: ", Replacement:="1A: "


Dim r2 As Range
Set r2 = Range("N2:N" & lastrow)
r2.Replace What:="1: ", Replacement:="1B: "

Option #2

Columns("J:J").Select
Selection.Replace What:="1: ", Replacement:="1A: "

Columns("N:N").Select
Selection.Replace What:="1: ", Replacement:="1B: "

 

[Fluff]

How about

Range("J:J").Replace "1: ", "1A: ", xlPart, , , , False, False
Range("N:N").Replace "1: ", "1B: ", xlPart, , , , False, False

 

[srdavisgb]

This code (e.g., Range("J:J").Replace "1: ", "1A: ", xlPart, , , , False, False) do what I'd like it to do...

When "1: " is replaced in column J with "1A: ", it (e.g., "1: ") is also replaced in column N. So, the code Range("N:N").Replace "1: ", "1B: ", xlPart, , , , False, False cannot find "1: " in column N.

What am I missing?

Thanks for your help.

 

[Fluff]

Do you have formulae in column N that looks at column J?
Also do you have any merged cells?