Problem with Do Loop

Garry (Wilson)

Thanks for the file- that nearly always helps us! Try to do that with future questions please

In the attached revised file, I've assigned a modified version of your macro (below) to the button labelled "Add New  Reservation for  Resident".

The changes made include:

1. declaring a new variable Tbl (to record the chosen table name)
2. disabling J1 cell selection and event disabling (not really needed with a macro doing only a few things)
3. restoring the Do/ Loop Until statements (to capture a response for each resident of a Villa)
4. adding InputBox code (which will offer a second resident the same as that chosen by the first, if they're attending).
5. removed several extra spaces in messages.

Main changes are in bold below:

Sub Macro7()

    Dim rVillaNo As Range, resName As String, Resp As String

    Dim Meal As VbMsgBoxResult, GFO As VbMsgBoxResult

    Dim Tbl As String


    Sheets("Residents").Select

    ActiveSheet.Unprotect

    'Range("j1").Select

    Resp = InputBox("What is the Resident's Villa No?.")

    '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 rVilla Is Nothing Then

            ' use the Do/ Loop Until to get other names in Villa...
            Do

                   Application.Goto Reference:=rVillaNo.Offset(0, -3), Scroll:=True

                    resName = rVillaNo.Offset(n, -2) & " " & rVillaNo.Offset(n, -1)

                    Meal = MsgBox("Is " & resName & " coming?", vbYesNo, "Who is coming From Villa " & Resp & "?")

                    Select Case Meal

                        Case vbYes

                        rVillaNo.Offset(n, -3).Value = 1

                        GFO = MsgBox(("Does " & resName & " require a Gluten Free meal ?"), vbYesNo, "Gluten Free Meal for Villa Number " & Resp)

                            If GFO = vbYes Then

                                 rVillaNo.Offset(n, 2) = "Y"

                             Else

                                 rVillaNo.Offset(n, 2).Value = ""

                            End If

                            ' this will offer same table as previous Villa resident but can be over-typed
                            Tbl = InputBox("Whose table will " & resName & " be sitting at? (Enter name)", "Requested table", Tbl)

                             ' write to column E
                             rVillaNo.Offset(n, 1).Value = Tbl

                         Case vbNo
                            ' clear booking entries
                            rVillaNo.Offset(n, -3).Value = ""
                            rVillaNo.Offset(n, 2).Value = ""
                            rVillaNo.Offset(n, 1).Value = ""

                         Case Else

                        'Exit Sub



                        End Select

                        n = n + 1

                        Loop Until rVillaNo.Offset(n, 0).Value <> rVillaNo.Value

                    End If

            End If

       'Application.EnableEvents = True

End Sub

Revision 02 November 2023:

Further to the user's follow-on question, the second attached file allows the user to complete a booking then make another (or quit).

It adds a new variable (near the start), in bold:

    Dim Tbl As String, Bck As VbMsgBoxResult

' marker for subsequent bookings
Coda:

and sets a place (randomly Coda, from music!) for the code to jump back to, if chosen.

The end of the code  has the changes in bold (with explanatory comments) 

        ' additional code to allow subsequent bookings  

        Bck = MsgBox(("Done! Want to make a booking for another Villa?"), vbYesNo, "Completed booking for Villa Number " & Resp)

        If Bck = vbYes Then
            ' reset variables
            Set rVillaNo = Nothing
            resName = ""
            Resp = ""
            Meal = vbNo
            GFO = vbNo
            Tbl = ""
            BckMeal = vbNo
            ' go back
            GoTo Coda

            ' if not, quit
            Else
            Exit Sub
        End If

       'Application.EnableEvents = True

End Sub

Hope this sorts things for you. If so, please remember to mark this Answer as Selected.