CODEWITHSUNDEEP
excelvbagoto
Mark Austin
Mark Austin

Reputation: 129

Error Handling not working VBA

I am trying to loop the user back if it could not find what you were looking for. I have that so far, but how do I loop back to have the user re-enter the info and prompt them with a MsgBox as well?

Range("A1").Select
Repeat:
Findcata = InputBox("Please Input What Section You Want To Add To")
If Findcata = "" Then Exit Sub
Set RangeObj = Cells.Find(What:=Findcat, After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False)
If RangeObj Is Nothing Then GoTo Repeat Else: RangeObj.Select

Upvotes: 2

Views: 127

Answers (1)

brettdj
brettdj

Reputation: 55672

The method below uses Application.Inputbox which

  • gives the ability to provide a default value
  • provides a method to update the MsgBox to be clear there is a retry

I have tidied the Select, Find logic as well.

Dim strIn As String
Dim strNew As String
Dim rng1 As Range

Repeat:
strIn = Application.InputBox("Please Input What Section You Want To Add To (Cancel Exits)", strNew, "Default value", , , , , 2)
If strIn = vbNullString Or strIn = "False" Then Exit Sub
Set rng1 = Cells.Find(strIn, ActiveSheet.[a1], xlValues, xlPart, xlByRows, xlNext, False)

If rng1 Is Nothing Then
strNew = "Value not found, please retry"
GoTo Repeat
End If

Upvotes: 1

Related Questions