Consulting

Results 1 to 2 of 2

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

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

    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
    Last edited by Aussiebear; 04-06-2023 at 09:40 PM. Reason: Added code tags to supplied code

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •