Error Loop Without Do

[Wilconcl51]

This is my code and for the life of me I am unable to have the outer loop work.

Sub Macro_Add_Residents()
   
    Dim rVillaNo As Range
    Dim resName As String
    Dim Resp As String
    Dim Meal As VbMsgBoxResult
    Dim GFO As VbMsgBoxResult
    Dim More As String
   
    Sheets("residents").Select
    Range("j1").Select
   
    Do
      Resp = InputBox("What is their Villa Number?")
     ' stop triggering other macro(s)
     Application.EnableEvents = False
   
    If Len(Resp) > 0 Then
        On Error Resume Next
        Set rVillaNo = Range("VillaNo").Find(What:=Resp, LookAt:=xlWhole, SearchDirection:=xlDown)
        If Not rVillaNo Is Nothing Then
           
            Do
                Application.Goto Reference:=rVillaNo.Offset(, -3), Scroll:=True
                ' collect one resiident's name
                resName = rVillaNo.Offset(n, -2) & " " & rVillaNo.Offset(n, -1)
                ' see if they want a meal
                Meal = MsgBox("Is   " & resName & " coming?", vbYesNo, "Add this Resident to List " & Resp)
                ' act on response
                Select Case Meal
                    Case vbYes
                        'if Yes, set value
                        rVillaNo.Offset(n, -3).Value = 1
                      
                        GFO = MsgBox("Gluten Free for " & resName & "?", vbYesNo, "Gluten Free Meal " & Resp)
                        ' chck if this resident needs GF
                        If GFO = vbYes Then
                            rVillaNo.Offset(n, 2).Value = "Y"
                            Else
                            rVillaNo.Offset(n, 2).Value = ""
                        End If
                    Case vbNo
                        'if No, clear value and payment cells
                        'rVillaNo.Offset(n, -3).Value = ""
                       ' rVillaNo.Offset(n, 4).Resize(1, 2).Interior.Color = 16777215
                        'rVillaNo.Offset(n, 2).Value = ""
                        Exit Sub
                    Case Else
                        Exit Sub
                End Select
                ' increment a counter
                n = n + 1
                ' go back if there's another resident in that villa
            Loop Until rVillaNo.Offset(n, 0).Value <> rVillaNo.Value
        End If
    End If
    More = MsgBox(" Add more Residents to the List", vbYesNo, "Continue to add Residents")
    Select Case More
      Case vbYes
         Loop
    Case vbNo
        Exit Sub
    End If
    End Select
      
    Application.EnableEvents = True
End Sub

 

[Legacy 143009]

I hope you are familiar with VBA codes.

The problem you have to solve is, your Select Case statement is partially in the loop, and partially outside the loop.
It looks something like this:

Do
  Select Case
  Case x
Loop
Case y
  Exit Sub
End Select

You can't do that.

It should look like either like this:

Select Case
Case x
  Do
  Loop
Case y
  Exit Sub
End Select

OR this:

Do
  Select Case
  Case x
  'Do something
  Case y
  Exit Sub
  End Select
Loop

 

[Wilconcl51]

I hope you are familiar with VBA codes.

The problem you have to solve is, your Select Case statement is partially in the loop, and partially outside the loop.
It looks something like this:

Do
  Select Case
  Case x
Loop
Case y
  Exit Sub
End Select

You can't do that.

It should look like either like this:

Select Case
Case x
  Do
  Loop
Case y
  Exit Sub
End Select

OR this:

Do
  Select Case
  Case x
  'Do something
  Case y
  Exit Sub
  End Select
Loop

Thank you much appreciated

 

[Legacy 143009]

You are welcome

 

[Wilconcl51]

You are welcome

Sorry to bother you again.
I am trying to add this bit of code within the loop so that it prompts the user to add the table name for each person taking a meal

rVillaNo.Offset(0, 1).Value = InputBox(" Whose Table will they be seated at?")

No matter where I place it it only updates the table name for the first person where there is more than one in a villa.

Sub Macro_Add_Residents()
   
    Dim rVillaNo As Range
    Dim resName As String
    Dim Resp As String
    Dim Meal As VbMsgBoxResult
    Dim GFO As VbMsgBoxResult
    Dim More As String
    Dim Table As String
   
    Sheets("residents").Select
    Range("j1").Select
   
    Resp = InputBox("What is their Villa Number?")
     ' stop triggering other macro(s)
     Application.EnableEvents = False
   
    If Len(Resp) > 0 Then
        On Error Resume Next
        Set rVillaNo = Range("VillaNo").Find(What:=Resp, LookAt:=xlWhole, SearchDirection:=xlDown)
        If Not rVillaNo Is Nothing Then
           
            Do
                Application.Goto Reference:=rVillaNo.Offset(, -3), Scroll:=True
                ' collect one resiident's name
                resName = rVillaNo.Offset(n, -2) & " " & rVillaNo.Offset(n, -1)
                ' see if they want a meal
                Meal = MsgBox("Is   " & resName & " coming?", vbYesNo, "Add this Resident to List " & Resp)
                ' act on response
                Select Case Meal
                    Case vbYes
                        'if Yes, set value
                        rVillaNo.Offset(n, -3).Value = 1
                                  
                 
                 
                        GFO = MsgBox("Gluten Free for " & resName & "?", vbYesNo, "Gluten Free Meal " & Resp)
                        ' chck if this resident needs GF
                        If GFO = vbYes Then
                            rVillaNo.Offset(n, 2).Value = "Y"
                            Else
                            rVillaNo.Offset(n, 2).Value = ""
                        End If
                    Case vbNo
                        'if No, clear value and payment cells
                        'rVillaNo.Offset(n, -3).Value = ""
                       ' rVillaNo.Offset(n, 4).Resize(1, 2).Interior.Color = 16777215
                        'rVillaNo.Offset(n, 2).Value = ""
                        Sheets("main Menu").Select
                    Case Else
                        Exit Sub
                       
                End Select
                 
                
                ' increment a counter
                n = n + 1
                ' go back if there's another resident in that villa
               
                
            Loop Until rVillaNo.Offset(n, 0).Value <> rVillaNo.Value
           
           
        End If
    End If
            
    More = MsgBox(" Add more Residents to the List", vbYesNo, "Continue to add Residents")
    If More = vbYes Then
  Macro_Add_Residents
 
    Else
       Sheets("Main Menu").Select
      
   End If
    Application.EnableEvents = True
   
End Sub

 

[Legacy 143009]

It would better if you open a new thread.
Also try to wrap your code with vba tags from the editing tools above.