How to Add Sheet if Name doesn't Exist Loop

Question

Hi I am trying to create a loop to see if an input matches an existing sheet's name.

If it does, then I want the sub to restart (So the user is asked for a new state).

If the input does not have a match, then I want a new sheet to be added. I have most of it done, but when it creates a new sheet, it r-eloops and just adds blank sheets.

Please let me know your thoughts!!

Sub partA()
Dim State As Worksheet
Dim StateName As String
Dim NameExist As Boolean
Dim HQ As String
Dim BO As Integer
Dim Sales As Integer

On Error Resume Next
'Asking for sheet name and then adding one if there is no match
StateName = InputBox("Please Enter a State Name", "State Name")

  For Each State In ActiveWorkbook.Worksheets
        If UCase(StateName) = UCase(State.Name) Then
           NameExist = True
    MsgBox "Worksheet " & StateName & " Exists"
           ElseIf NameExist = False Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = StateName
        End If
    Next State

End Sub

Answer 1

Just wrap it in a Do...Loop and exit after you find a unique name:

Sub partA()
    Dim State As Worksheet
    Dim StateName As String
    Dim NameExist As Boolean
    Dim HQ As String
    Dim BO As Integer
    Dim Sales As Integer

    'Asking for sheet name and then adding one if there is no match
    Do
        StateName = InputBox("Please Enter a State Name", "State Name")
        NameExist = False
        For Each State In ActiveWorkbook.Worksheets
            If UCase(StateName) = UCase(State.Name) Then
                NameExist = True
                MsgBox "Worksheet " & StateName & " Exists"
                Exit For
            End If
        Next State
    Loop Until NameExist = False

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = StateName

End Sub

Answer 2

Better to test directly for the sheet existing than looping over every sheet.

Also I have removed redundant variables, and changed your InputBox (to force a string).

Sub ReCut()
Dim State As Worksheet
Dim StateName As String

StateName = Application.InputBox("Case Sensitive", "Please Enter a State Name", , , , , , 2)
On Error Resume Next
Set State = Sheets(StateName)
On Error GoTo 0

If State Is Nothing Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = StateName
Else
    MsgBox "Worksheet " & StateName & " Exists"
End If

End Sub

Answer 3

Maybe this Sub and this Function could you help.

Function Find_Sheet(Name_Sheet As String) As Boolean
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = Name_Sheet Then
                   Find_Sheet = True
            Exit Function
        End If
    Next
    Find_Sheet = False
End Function

Sub MySub()

Dim Name_Sheet As String
Name_Sheet = Range("a1").Value
Var = Find_Sheet(Name_Sheet)
If Var = True Then
    MsgBox "It Sheet Exists"
Else
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Name_Sheet
    MsgBox "Sheet " & Name_Sheet & " was created"
End If
End Sub