Search a text in a range and replace with different text to adjacent cell

[Dinesh_Excel]

Hi Friend,

I am trying to find a text in a range say column "A" and replace the same with a different text in a separate row adjacent to that text.

I tried the below code but it has a blank on the first row and adding "Red" in place where there is blank.

Appreciate your help

Option Explicit

Sub Replace_Text()

Dim last row As Long, rng As Range

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False

With Sheet1
.AutoFilterMode = False
Set rng = .Range("A2:A" & lastrow)
rng.AutoFilter field:=1, Criteria1:="Apple*"
rng.Offset(1, 5).SpecialCells(12).Value = "Red"
.Cells(lastrow + 1, 1) = ""
.AutoFilterMode = False

End With

Application.ScreenUpdating = True

Set rng = Nothing

End Sub

 

[YasserKhalil]

Change this line to start with A1

Set rng = .Range("A1:A" & lastrow)

 

[Dinesh_Excel]

Thanks, Yasser but the last row is showing as red in spite the cell being blank..where am I going wrong

 

[Chihiro]

Since lastRow = row# of last cell in Column A with data
=6

So you are setting 6 rows of value to "Red".

But since you have .Offset(1, 5) meaning offset range by 1 row and 5 columns.

"Red" is entered to rows 2 to 7 in column 6.

You should, first offset range by 1 to exclude header row, then resize it by lastrow - 1.

This will shrink down range to 2 to 6. Then you select only the visible cells and then offset column by 5.

So something like...

Option Explicit
Sub Replace_Text()
  
    Dim lastrow As Long, rng As Range
  
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
  
    Application.ScreenUpdating = False
  
    With Sheet1
        .AutoFilterMode = False
        Set rng = .Range("A1:A" & lastrow)
        rng.AutoFilter field:=1, Criteria1:="Apple*"
        rng.Offset(1).Resize(lastrow - 1).SpecialCells(12).Offset(, 5).Value = "Red"
        .AutoFilterMode = False
      
    End With
  
    Application.ScreenUpdating = True
  
    Set rng = Nothing
  
End Sub