1

I’m not very good in VBA, but have found a lot of great stuff here, so please be patient. I have tried to take a look at other answers for similar problems, but don’t understand what the solution is. I get a runtime error 91 when looping this code. In one tab of my Excel document, I have a list of member names and attributes that are all located in column A based on how I pasted them in. Here is an example:

Member Name

Joe Smith

Member Role

President

Member Name

Sheila JonesAuthorized

Member Role

Treasurer

Member Name

Bill JohnsonAuthorized

Vice President

I am looking to get them to be pasted into another tab with all the names in column A and their respective roles in column B. Once Excel reaches the end of the list, I then want it to “clean up” the names by removing the text “Authorized” from the cells. This individual code all works fine, but I cannot get it to perform in a loop without coming back with a runtime 91 error. Since I have many of these lists, I was hoping to automate it, so that I could paste a list into one tab, click a button, generate a list, and then repeat with another list. What am I missing? Thanks so much for your help!

Sub Button1_Click()

Worksheets("Paste List Here").Activate
Do
Cells.Find(What:="Member Name", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    True, SearchFormat:=False).Activate

    ActiveCell.Delete
'this deletes the cell containing “Member Name”, so that when I loop the process, it will go onto the next name
    ActiveCell.Copy


Worksheets("Final List").Activate

   ActiveSheet.Paste
   ActiveCell.Offset(0, 1).Select


   Worksheets("Paste List Here").Activate

Cells.Find(What:="Member Role", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    True, SearchFormat:=False).Activate

    ActiveCell.Delete
'this deletes the cell containing “Member Role”, so that when I loop the process, it will go onto the next name

    ActiveCell.Copy


Worksheets("Final List").Activate

   ActiveSheet.Paste
   ActiveCell.Offset(1, -1).Select

Worksheets("Paste List Here").Activate

Loop Until ActiveCell = ""

Worksheets("Final List").Activate

Worksheets("Final List").Columns("A").Replace _
What:="Authorized", Replacement:=" ", _
SearchOrder:=xlByColumns, MatchCase:=True
'this deletes the text within a cell to leave just the name


End Sub
J. Davis
  • 11
  • 1
  • 2
    I highly suggest avoiding the use of `.ActiveCell`/`.Activate`. [Here's a good SO question/answer](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) which should help with the issue. It's likely largely due to the use of `.Activate`/`.Select`, and VBA not correctly resetting/changing the ActiveCell. Where does the error occur? What line throws the error. – BruceWayne Jun 08 '17 at 20:54
  • 99% sure your error is occurring on the `Cells.Find` line. `Find` returns `Nothing` whenever it finds nothing, and you can't do `Nothing.Activate` (or `Nothing.Anything` actually) without getting run-time error 91 - your code assumes `Find` will find something. Split that instruction up and verify whether the result `Is Nothing` before you `Activate` it.... **if** you *need* to activate it at all. Which I doubt. – Mathieu Guindon Jun 08 '17 at 21:12
  • Thanks everybody! I changed the code to the following: Set Cell = Cells.Find(What:="Member Name", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=True, SearchFormat:=False) If Cell Is Nothing Then Exit Do Else Cell.Select ActiveCell.Delete ActiveCell.Copy End If I know this is not perfect (still using ActiveCell) but is functional. Thanks again! – J. Davis Jun 12 '17 at 17:37

0 Answers0