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