Create Account

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Q&A Forum

Macro. Find a Specific Cell in a Named Range

My worksheet has named range "Villa". Villa numbers can appear more that once depending on number of residents in each villa with max. of 2.

Trying to create a macro that will
1. Prompt User to input Villa No.
2. If Villa No not found then back to 1. ( That's because Villas are currently numbered 1-270; 401-428; 801-858)
3. Locate the Villa No. and moves one cell to right
4. Prompts User to input either " " or "1"
5. Move another one cell to right
6. Prompts User to input either "Y" or ""
7. Loops back to 1. above unless User Exits

Ideally the User should not be able to exit until 7.

Many thanks

excel

MS Office Version: 2016

posted by: Wilsons51 (rep: 4) on Tue Oct 24, 2023 at 5:40 am

Answer

Discuss

Discussion

Hi Wilson and welcome to the Forum

To save our time, kindly show us example data and how far you have got on creating your macro- please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data and macros. Then we should be able to give specific help.

John_Ru (rep: 6142) Oct 24, '23 at 8:23 am

Wilson. Thanks for modifying your question but that (xml code?) doesn't help, sorry. You need to attach an Excel file (as I said).

John_Ru (rep: 6142) Oct 25, '23 at 1:08 am

Wilson. Thanks for the file but it looks like you've made no attempt to write a macro (or did you attach the wrong file?). If not, it's like you're asking us to wriye the code for you (which is not how the Q&A Forum works generally).

Your question text doesn'tstill make full sense- which columns are to be filled? ( E.g. "one cell to right" of Villa number 1 cell D10 is FirstName E10 where " " or "1" makes no sense- should that be in column A or G perhaps?). Also what to do if the Villa number is on two or more lines?

John_Ru (rep: 6142) Oct 25, '23 at 10:10 am

Thank you Joh. I have worked it out
Here is the part that works
Dim rVillaNo As Range Dim Resp As String Dim Meal As String Dim GFO As String Dim Msg, Style, Title, Help, Ctxt, Response, MyString As String Resp = InputBox("What is their Villa Number. Villa Number only?. Ctrl+ Shift+ A to start over") Application.ScreenUpdating = False 'Range("J2").Select If Len(Resp) > 0 Then On Error Resume Next Set rVillaNo = Range("VillaNo").Find(What:=Resp, LookAt:=xlWhole) On Error GoTo 0 If Not rVillaNo Is Nothing Then Application.Goto Reference:=rVillaNo.Offset(, -3), Scroll:=True End If Meal = InputBox("Enter 1 if they are attending") ActiveCell = Meal ActiveCell.Offset(0, 2).Select GFO = InputBox("Input Y for Gluten Free or Enter") ActiveCell = GFO

Wilsons51 (rep: 4) Oct 25, '23 at 5:18 pm

Thanks Wilson

I put the returns into your (cluttered) code above to see what you were doing and used part of it in my Answer (below). Next time, please click the CODE button (next to symbols for Bold and Italic) then paste your code over the "Code_Goes_Here" bit.

John_Ru (rep: 6142) Oct 28, '23 at 6:22 am

Add to Discussion

Answers

Selected Answer

Wilson

I used some of your (cluttered) code (in the Discussion above) in my version below and within the attached file..

You'll see a new green button"Enter resident's meal needs" near the top of the worksheet. That's assigned to the modified code below (commented to explain what's happening):

Sub PickVilla()
    ' Revised code extract
    Dim rVillaNo As Range
    Dim resName As String
    Dim Resp As String
    Dim TuesMeal As VbMsgBoxResult, TuesGFO As VbMsgBoxResult

    Resp = InputBox("What is the resident's Villa Number?", "Please input the Villa Number only")
    ' 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
            ' see what the resident(s) need
            Do
                Application.Goto Reference:=rVillaNo.Offset(, -3), Scroll:=True
                ' collect one resiident's name
                resName = rVillaNo.Offset(n, 1) & " " & rVillaNo.Offset(n, 2)
                ' see if they want the Tuesday meal
                TuesMeal = MsgBox("Will resident " & resName & _
                    " take the Tuesday meal?", vbYesNo, "Meal requirements for Villa Number " & Resp)
                ' act on response
                Select Case TuesMeal
                    Case vbYes
                        'if Yes, set value and highlight payment cells
                        rVillaNo.Offset(n, -3).Value = 1
                        rVillaNo.Offset(n, 4).Resize(1, 2).Interior.Color = vbYellow
                        TuesGFO = MsgBox("Gluten-free for " & resName & "?", vbYesNo, "Tuesday meal Dietary requirements for Villa Number " & Resp)
                        ' chck if this resident needs GF
                        If TuesGFO = vbYes Then
                            rVillaNo.Offset(n, -1).Value = "Y"
                            Else
                            rVillaNo.Offset(n, -1).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, -1).Value = ""
                    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
    Application.EnableEvents = True
End Sub

If you click on it and enter a valid Villa Number, it will go to that row (I froze the top rows so the headers show). It will then ask (for each resident) if they want the meal on Tuesday and (if so) if that's Gluten-free. It will enter 1 and Y respectively for a Yes response and highligh cells in H and I in yellow (to show a payment by cash or by electronc transfer is needed).

I added an "event" macro which checks if either payment is made (some entry other than nothing in the yellow cells) and if so it clears the yellow:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' check if change is in column H or I
    If Not Intersect(Target, Columns("H:I")) Is Nothing Then
        ' if one is non-blank then clear formatting
        If Cells(Target.Row, 8) & Cells(Target.Row, 9) <> "" Then
            Cells(Target.Row, 8).Resize(1, 2).Interior.Color = 16777215
        End If

        Else
        ' or if it's J or K...
        If Not Intersect(Target, Columns("J:K")) Is Nothing Then
            ' if one is non-blank then clear formatting
            If Cells(Target.Row, 10) & Cells(Target.Row, 11) <> "" Then
                Cells(Target.Row, 10).Resize(1, 2).Interior.Color = 16777215
            End If
        End If
    End If

End Sub

That code is extended to cover changes in J & K (payment cells for Friday), but I leave you to do that in the PickVilla code fr Friday (largely copying code and changing the offsets within the Do/ Loop Until block).

You should be able to use it for many more Villas but be sure to extend the definition of Named range Villa (noting that I had to change if from =Residents!$D$10:$D$26 to =Residents!$D$9:$D$26 since you used .Find and for Villa 1, that landed on row 11 not 10).

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Sat Oct 28, 2023 at 6:44 am

Discuss

Discussion

Guess that worked for you, Wilson. Thanks for selecting my Answer.

John_Ru (rep: 6142) Oct 28, '23 at 6:41 pm

Add to Discussion

Excel VBA Course - From Beginner to Expert

Macro. Find a Specific Cell in a Named Range

Discussion

Answers

Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login