Input Box Macro

[Wilconcl51]

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 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

 

[Michael M]

Maybe something like

Sub MM1()
Dim Val As Variant
Val = InputBox("Please Insert Villa Number")
Select Case Val
    Case Is <= 270
    Cells(2, 1) = Val
    Case 401 To 428
    Cells(2, 1) = Val
    Case 801 To 858
    Cells(2, 1) = Val
    Case Else
    MsgBox ("incorrect Villa Number, Please start again")
    Exit Sub
End Select
Val = InputBox("Please Leave blank or Enter the number 1")
Cells(2, 2) = Val
Val = InputBox("Please Yes or leave Blank")
Cells(2, 3) = Val
End Sub

 

[Wilconcl51]

Thanks Michael.
The Input Boxes work except that the input data is placed in Row 2 and Not in the row for the Villa I input
Garry

 

[Michael M]

Sub MM1()
Dim Val As Variant
Val = InputBox("Please Insert Villa Number")
Select Case Val
    Case Is <= 270
    Cells(Val, 1) = Val
    Case 401 To 428
    Cells(Val, 1) = Val
    Case 801 To 858
    Cells(Val, 1) = Val
    Case Else
    MsgBox ("incorrect Villa Number, Please start again")
    Exit Sub
End Select
ans = InputBox("Please Leave blank or Enter the number 1")
Cells(Val, 2) = ans
ans2 = InputBox("Please Yes or leave Blank")
Cells(Val, 3) = ans2
End Sub

 

[Wilconcl51]

Sub MM1()
Dim Val As Variant
Val = InputBox("Please Insert Villa Number")
Select Case Val
    Case Is <= 270
    Cells(Val, 1) = Val
    Case 401 To 428
    Cells(Val, 1) = Val
    Case 801 To 858
    Cells(Val, 1) = Val
    Case Else
    MsgBox ("incorrect Villa Number, Please start again")
    Exit Sub
End Select
ans = InputBox("Please Leave blank or Enter the number 1")
Cells(Val, 2) = ans
ans2 = InputBox("Please Yes or leave Blank")
Cells(Val, 3) = ans2
End Sub

Hi Michael,

I tested using Villa 199. Data was placed in Row 199 which does not correspond to Villa 199 in my range. It appears the Code is not looking for Villa # in the Range ("Villa")

Garry

 

[Michael M]

UNTESTED

Sub MM1()
Dim Val As Variant, a As Range
Val = InputBox("Please Insert Villa Number")
With ThisWorkbook.Names("Villa").RefersToRange
    Set c = .Find("Villa " & Val, LookIn:=xlValues)
     If c Is Nothing Then MsgBox "Customer not found"
End With
ans = InputBox("Please Leave blank or Enter the number 1")
Range(c.Address).Offset(, 1) = ans
ans2 = InputBox("Please Yes or leave Blank")
Range(c.Address).Offset(, 2) = ans2
End Sub

 

[Wilconcl51]

UNTESTED

Sub MM1()
Dim Val As Variant, a As Range
Val = InputBox("Please Insert Villa Number")
With ThisWorkbook.Names("Villa").RefersToRange
    Set c = .Find("Villa " & Val, LookIn:=xlValues)
     If c Is Nothing Then MsgBox "Customer not found"
End With
ans = InputBox("Please Leave blank or Enter the number 1")
Range(c.Address).Offset(, 1) = ans
ans2 = InputBox("Please Yes or leave Blank")
Range(c.Address).Offset(, 2) = ans2
End Sub

Hi Michael,
When I input a known Villa No it comes up with "Customer Not Found. prompts for Next Input
Then stalls at Range(c.Address).Offset(, 1) = ans "object variable or With block variable not set

 

[Michael M]

Can you post a sample of your named range data pls

 

[Wilconcl51]

Can you post a sample of your named range data pls

Weekly meals.xlsm
	A	B	C	D	E	F	G	H	I	J	K
6
7
8
9	Tuesday Meals	Friday Meals	Gluten_Free (Y)	VillaNo	First_Name	Last_Name	Regulars (Y)	Tuesday Cash	Tuesday Eftpos	Friday Cash	Friday Eftpos
10				1	Desley	Cottam
11				1	Maurice	Scott
12				2	Margaret	Roberts
13				3	Cathy	Thomson
14				4	Kathy	Weisenberger
15				5	Ray	White
16				5	Beryl	White
17				6	Penny	Steele
18				6	David	Steele
19				7	Lorraine	McPhee
20				8	John	Beadle
21				8	Sue	Beadle
22				9	Maureen	Brady
23				10	Ann	Shaw
24				10	Dennis	Shaw
25				11	Ted	Dynes
26				11	Carol	Dynes
27				12	Grace	Ambrose
28				13	Colin	Davey
29				13	Cheryl	Davey
30				14	Iris	Sing
31				14	Graeme	Sing
32				15	Val	Voyzey
33				16	Robert	Haste
34				16	Faye	Haste
35				17	Ray	Church
36				17	Larraine	Church
37				18	Arthur	Perry
38				18	Shirley	Perry
39				19	Sintra	Wooding
40				20	Ted	Dwyer
41				20	Sue	Dwyer
Residents

Cells with Data Validation
Cell	Allow	Criteria
C10:C41	Text length	between 0 and 1