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

 

[My Aswer Is This]

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

So, you have 500 named ranges on the same workbook, correct?
So, if you have a named Range of "Alpha" and you enter "Alpha" in Input box you want the active cell to now be 3 cells to the right of that named range. Is this correct?

 

[Wilconcl51]

So, you have 500 named ranges on the same workbook, correct?
So, if you have a named Range of "Alpha" and you enter "Alpha" in Input box you want the active cell to now be 3 cells to the right of that named range. Is this correct?

Named Range is "Villa" Range D2:D520 Worksheet is "Residents"
Need User from an Input Box find specific Villa say "189" then go the cell 3 columns to the left.

 

[My Aswer Is This]

You said:
Range D2:D520 Worksheet is "Residents"

So, you want to search the Range D2:D520 in sheet named "Residents"
For the value you enter into InputBox Is that correct?

 

[Wilconcl51]

You said:
Range D2:D520 Worksheet is "Residents"

So, you want to search the Range D2:D520 in sheet named "Residents"
For the value you enter into InputBox Is that correct?

Yes. Then go to that cell and then move 3 columns to the left.

 

[Peter_SSs]

Give this a try.
I have assumed that the named range "Villa" has 'Workbook' scope.

Sub Test()
  Dim rVilla As Range
  Dim Resp As String
 
  Resp = InputBox("What Villa?")
  On Error Resume Next
  Set rVilla = Range("Villa").Find(What:=Resp, LookAt:=xlWhole)
  On Error GoTo 0
  If rVilla Is Nothing Then
    MsgBox "Villa " & Resp & " not found"
  Else
    Application.Goto Reference:=rVilla.Offset(, -3), Scroll:=True
  End If
End Sub

 

[Wilconcl51]

Give this a try.
I have assumed that the named range "Villa" has 'Workbook' scope.

Sub Test()
  Dim rVilla As Range
  Dim Resp As String
 
  Resp = InputBox("What Villa?")
  On Error Resume Next
  Set rVilla = Range("Villa").Find(What:=Resp, LookAt:=xlWhole)
  On Error GoTo 0
  If rVilla Is Nothing Then
    MsgBox "Villa " & Resp & " not found"
  Else
    Application.Goto Reference:=rVilla.Offset(, -3), Scroll:=True
  End If
End Sub

That is excellent. Thank you so much

 

[My Aswer Is This]

There you go you now have an answer. I will move on to help someone else

 

[Peter_SSs]

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

 

[Wilconcl51]

That is excellent. Thank you so much

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

How do I fix this

Garry

Sub Macro7()

  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
    MsgBox "Villa " & Resp & " not found"
    'End If
  Else
    Application.Goto Reference:=rVilla.Offset(, -3), Scroll:=True
 End If

End Sub