Vba to find a Cell from Input Box the move 3 colums to the left

[Wilconcl51]

I am new to Excel and this is doing my head in

I have Named Range of 500 Villas. I seek to move to cell where user inputs Villa Number, then move three colums to the left.

Much appreciated

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

My code is as follows and works well except when user hits cancel button or ok without inputting data

In that circumstance, your code returns a message box saying "Villa not found".
What do you want to happen if the user does either of those things?

 

[Wilconcl51]

Return to Range J2. which is where I have my Buttons

 

[Peter_SSs]

Return to Range J2. which is where I have my Buttons

Thanks, try this version

Sub Macro7_Updated()
  Dim rVilla As Range
  Dim Resp As String
 
  Resp = InputBox("What is their Villa Number.  Villa Number only?. Ctrl+ Shift+ A to start over")
  On Error Resume Next
  Set rVilla = Range("Villa").Find(What:=Resp, LookAt:=xlWhole)
  On Error GoTo 0
  If rVilla Is Nothing Then
    Range("J2").Select
  Else
    Application.Goto Reference:=rVilla.Offset(, -3), Scroll:=True
 End If
End Sub

 

[Wilconcl51]

If I input either Cancel, or OK, goes to the end of my Range "Villa"
Then moves 3 columns to the left

 

[Peter_SSs]

If I input either Cancel, or OK, goes to the end of my Range "Villa"
Then moves 3 columns to the left

That would indicate that your named range "Villa" contains 1 or more blank cells.
Try this instead.

Sub Macro7_Updated_v2()
  Dim rVilla As Range
  Dim Resp 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 rVilla = Range("Villa").Find(What:=Resp, LookAt:=xlWhole)
   On Error GoTo 0
   If Not rVilla Is Nothing Then Application.Goto Reference:=rVilla.Offset(, -3), Scroll:=True
 End If
 Application.ScreenUpdating = True
End Sub

 

[Wilconcl51]

That would indicate that your named range "Villa" contains 1 or more blank cells.
Try this instead.

Sub Macro7_Updated_v2()
  Dim rVilla As Range
  Dim Resp 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 rVilla = Range("Villa").Find(What:=Resp, LookAt:=xlWhole)
   On Error GoTo 0
   If Not rVilla Is Nothing Then Application.Goto Reference:=rVilla.Offset(, -3), Scroll:=True
 End If
 Application.ScreenUpdating = True
End Sub

Hi Peter,

Many thanks. It works very well.
Your assistance is much appreciated.

Garry

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Wilconcl51]

Hi Peter,

Many thanks. It works very well.
Your assistance is much appreciated.

Garry

Hi Peter,

A few more questions.

Once code take me to the Villa No in input,
How can I
Prompt for Input of say "1"
Move right by one cell
Prompt for input "Y" or " "
Then loop back to Resp = InputBox("What is their Villa Number
Continue with this till exit

Garry