crkennedy
10-17-2018, 06:33 AM
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
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