VBA: Find and Replace Help

[NeoSez]

What is wrong with this code?
I am only searching in one column.
I want a full match with "NEST" only and not partial string matches.
For example it found NEST in "Big Bird Nest" and replaced it with "Big Bird Nest" again, so the result came out "Big Big Bird Bird Nest Nest". How do I make it only replace when it finds only one NEST in the cell?
I tried to add

LookAt:=xlWhole

but it didn't work.
Other than that the VBA script works great for finding & replacing.

'   Replace NEST,
    myRange.Replace what:="NEST", Replacement:="Big Bird Nest", lookat:=xlPart, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False,

 

[Logit]

The following will do it but you'll need to edit the code to fit your "range" :

Option Explicit

Sub FindnReplace()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    
    Dim myString As String

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
 
    
    myString = "NEST"
    
    For Each c In Source.Range("A2:A1000")   ' Do 1000 rows
        If c = myString Then
           c.Value = "Big Bird Nest"
        End If
    Next c
End Sub

Tested here.

 

[NeoSez]

The following will do it but you'll need to edit the code to fit your "range" :

Option Explicit

Sub FindnReplace()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
  
    Dim myString As String

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")

  
    myString = "NEST"
  
    For Each c In Source.Range("A2:A1000")   ' Do 1000 rows
        If c = myString Then
           c.Value = "Big Bird Nest"
        End If
    Next c
End Sub

Tested here.

Thanks for the code.

However, I was hoping to just modify the script I already have as it is rather lengthy with many search and replace items. Is that possible to just modify this script? I'd do it but I'm a noob and will screw it up.

Sub Replace()
    Dim lastRow As Long
    Dim myRange As Range
   
'   Find lastRow in column F
    lastRow = Cells(Rows.Count, "F").End(xlUp).Row

'   Set range to look at
    Set myRange = Range("F2:F" & lastRow)

'   Replace NEST,
    myRange.Replace what:="NEST", Replacement:="Big Bird Nest", lookat:=xlPart, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False,

 

[Logit]

What happens if you change : lookat:=xlPart to lookat:=xlWhole

 

[Peter_SSs]

I tried to add

LookAt:=xlWhole

but it didn't work.
Other than that the VBA script works great for finding & replacing.

'   Replace NEST,
    myRange.Replace what:="NEST", Replacement:="Big Bird Nest", lookat:=xlPart, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False,

You shouldn't add that code, it should replace the lookat:=xlpart

myRange.Replace what:="NEST", Replacement:="Big Bird Nest", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[NeoSez]

You shouldn't add that code, it should replace the lookat:=xlpart

myRange.Replace what:="NEST", Replacement:="Big Bird Nest", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Thank you Peter. I totally missed that. Works now.

 

[Peter_SSs]

You're welcome. Glad it is sorted.
BTW, Logit did suggest the same thing a moment before I posted.

 

[NeoSez]

You're welcome. Glad it is sorted.
BTW, Logit did suggest the same thing a moment before I posted.

Yes, I saw Logit's message after I posted. Thank you for your time Logit .

 

[Logit]

Glad you have an answer !