VBA Code to loop once (Do While) Replacing Array, Object Error

[crkennedy]

Hi All

I'm wanting to replace cells in column E with the rplcList Array in my spreadsheet with the rplcList Array.
I only want the code to loop once, hence why i'm using the do while loop.
I've pieced together code from online which normally works for me, but I'm no expert.

Dim y As Long
fndList = Array("C", "1", "C1", "C2", "")
rplcList = Array("Tom", "John", "Paul" & " " & "Smith", "Clarke", "Claire")
'Loop through each worksheet in ActiveWorkbook
Range("E2").Select.Cells. _
Replace What:=fndList(y), Replacement:=rplcList(y), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Do Until IsEmpty(ActiveCell)
'Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop

I'm getting the object required error and I have tried to define the object myself multiple times but this is obviously an oversight on my part.
I'm sure the answer is clear, however I'd appreciate any tips.

Much Appreciated

[Paul_Hossler]

I think you were missing a few steps

Try this

Option Explicit

Sub Test()
    Dim ws As Worksheet
    Dim rText As Range
    Dim y As Long
    Dim fndList As Variant, rplcList As Variant

    fndList = Array("C", "1", "C1", "C2", "")
    rplcList = Array("Tom", "John", "Paul" & " " & "Smith", "Clarke", "Claire")

    ' Loop through each worksheet in ActiveWorkbook
    For Each ws In ActiveWorkbook.Worksheets
        
        'make sure that there are text values
        Set rText = Nothing
        On Error Resume Next
        Set rText = ws.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
        On Error GoTo 0
        
        'if no text then go on to next sheet
        If rText Is Nothing Then GoTo TryNextSheet
    
        For y = LBound(fndList) To UBound(fndList)
            'changed LookAt to xlWhole
            rText.Replace What:=fndList(y), Replacement:=rplcList(y), _
                LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
        Next y
        
TryNextSheet:
    Next
    
End Sub