Handling Multiple Errors within one loop

[SlinkRN]

I am trying to work with a loop so that if an error occurs when searching for a name (name is not found in list), it will then skip some of the lines of the loop and continue on further down in the loop. Unfortunately, it works for the first error but then not again. I simplified the loop as much as I could to keep the code here in the post to a minimum. I cannot use Resume Next because if the name is not found I don't want to enter scores anywhere. Where can I put code that will reset the On Error part so it will work again the next time a name isn't found. I searched the forum but I just can't get it to work. Thanks! Slink

Sub simpleloopwitherrors()
Dim i As Integer
Dim Bowler As String
Dim ans As Integer
Bowler = "Start"
i = 1
Do Until Bowler = ""
    Bowler = Sheets("Bowler").Range("A" & i)
    Sheets("Scores").Select
    Range("A1:A10").Select
    On Error GoTo noname
        Selection.Find(What:=Bowler, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 2).Value = 100
    ActiveCell.Offset(0, 3).Value = 200
    ActiveCell.Offset(0, 4).Value = 300
    GoTo nextbowler:  'this skips the noname error code
noname:
ans = MsgBox("The name " & Bowler & " does not exist (or is misspelled). Would you like to skip this bowler?", vbYesNo, "Name not found")
    If ans = vbYes Then
          GoTo nextbowler
        Else
    Exit Sub
    End If
nextbowler:

i = i + 1

Loop
   
End Sub

 

[6StringJazzer]

I don't think you should be using error handling in this case to manage your logic, because failure to find something is not really an error, it's a normal condition that you should expect could happen. I also recommend against using selection, activation.

Honestly I'm not clear on your question. You are exiting the sub if the user answers "No", so I'm not clear on what it is you want to do. But here is a rewrite of your code. If this doesn't solve your problem then it should make it more straightfoward to solve.

Sub simpleloopwitherrors()

   Dim i As Integer
   Dim Bowler As String
   Dim ans As Integer
   Dim Found As Range
  
   Bowler = "Start"
   i = 1
  
   Bowler = Sheets("Bowler").Range("A" & i)
  
   Do Until Bowler = ""
      
       Set Found = Sheets("Scores").Range("A1:A10").Find(What:=Bowler, After:=Found, LookIn:=xlFormulas, LookAt _
           :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
           False, SearchFormat:=False)
          
      If Found Is Nothing Then
         ans = MsgBox("The name " & Bowler & " does not exist (or is misspelled). Would you like to skip this bowler?", vbYesNo, "Name not found")
         If ans <> vbYes Then
            Exit Sub
         End If
      Else
         Found.Offset(0, 2).Value = 100
         Found.Offset(0, 3).Value = 200
         Found.Offset(0, 4).Value = 300
      End If
  
      i = i + 1
      Bowler = Sheets("Bowler").Range("A" & i)
  
   Loop
  
End Sub

 

[SlinkRN]

Thanks so much for your reply! This sounds exactly like what I was looking for! Unfortunately, I'm getting a Run-time error '13': Type mismatch when it gets to the Set Found line. Here is my actual range for this section. Does it change things that my actual range is bigger than my sample code?

Set Found = Sheets("Team_" & TeamNumber).Range("A3:AG55").Find(What:=Bowler, After:=Found, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

 

[Alex Blakenburg]

Try removing this
"After:=Found,"

 

[SlinkRN]

Yay! That got rid of the error, just working on getting the rest of my statements fixed up so it does what I need it to do. I'll let you know if I run into any other errors! Thanks so much! The Found statement really helps!!

 

[SlinkRN]

Try removing this "After:=Found,"

Try removing this
"After:=Found,"

Hi again Alex and Jeff! I love the using the Found option but I'm wondering if I can get the cell address of where the name was found. Right now, the value of Found is the bowler's name and I need to know exactly where that bowler's name is on the sheet. Is there an easy way to get the cell address as the value of Found? Thanks, Slink

 

[Alex Blakenburg]

"Found" is a range > Dim Found As Range
So Found.Address gives you the address.

You can also use it like this:

    Found.Offset(0, 2).Value = 100
    Found.Offset(0, 3).Value = 200
    Found.Offset(0, 4).Value = 300

 

[SlinkRN]

Ah, I see that now. Jeff did give me that code but when I was having loop errors I put the statement msgbox = Found and it showed it as a name so I thought that was the problem. I see now that it will work as a range. I just need to keep working on my loops until the whole thing works. Thanks again for your help!