Error Loop Without Do

Wilconcl51

New Member
Joined
Oct 10, 2023
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
This is my code and for the life of me I am unable to have the outer loop work.

Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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:
VBA Code:
Do
  Select Case
  Case x
Loop
Case y
  Exit Sub
End Select
You can't do that.

It should look like either like this:
VBA Code:
Select Case
Case x
  Do
  Loop
Case y
  Exit Sub
End Select
OR this:
VBA Code:
Do
  Select Case
  Case x
  'Do something
  Case y
  Exit Sub
  End Select
Loop
 
Upvote 0
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:
VBA Code:
Do
  Select Case
  Case x
Loop
Case y
  Exit Sub
End Select
You can't do that.

It should look like either like this:
VBA Code:
Select Case
Case x
  Do
  Loop
Case y
  Exit Sub
End Select
OR this:
VBA Code:
Do
  Select Case
  Case x
  'Do something
  Case y
  Exit Sub
  End Select
Loop
Thank you much appreciated
 
Upvote 0
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.

VBA Code:
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
 
Last edited by a moderator:
Upvote 0
It would better if you open a new thread.
Also try to wrap your code with vba tags from the editing tools above.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top