I am trying to loop this program but only if the state name entered doesn't match any of the tabs (labeled by states)

    'searches worksheets for state value
    For Each c In Worksheets
    Do
        'asks user for a state name
        sName = InputBox("Enter a state:")
            If sName = c.Name Then
                Exit Sub
        End If
    Next c
    
    'insert state on Allstates page
    wsAllStates.Select 'start on AllStates page
    Range("A1").Select 'starting point on cell
            
    ActiveCell.End(xlDown).Offset(1, 0).Select 'find the name of the bottom state
    ActiveCell.Value = sName 'write in the name of the state

    'add the new sheet
    Sheets.Add after:=Sheets(Sheets.Count)      'create a new sheet
    ActiveSheet.Name = sName            'and name it

    With ActiveSheet.Range("A1")
    'asks user for information
        sHead = InputBox("Enter the state's headquarters:")
        Branches = InputBox("Enter the amount of brand offices:")
        s2019 = InputBox("Enter the amount of sales in 2022:")

        'places information in appropriate cells with identifiers
        .Offset(0, 0).Value = "Headquarters" 'set value
        .Offset(0, 1) = sHead
        .Offset(1, 0).Value = "Branch offices" 'set value
        .Offset(1, 1) = Branches
        .Offset(2, 0).Value = "Sales in 2022" 'set value
        .Offset(2, 1) = s2019
    End With

'return to Allstates page
wsAllStates.Activate
Range("A1").Select

End Sub

CodePudding user response：

Nested Do and For...Each...Next Loops

Option Explicit


Sub DoForEachNextLoopSimple()
    
    Dim ssh As Object
    Dim sName As String
    Dim SheetFound As Boolean
    
    Do
        sName = InputBox("Enter a state:")
        If Len(sName) = 0 Then Exit Sub
        
        SheetFound = False
        For Each ssh In Sheets
            If StrComp(ssh.Name, sName, vbTextCompare) = 0 Then
                SheetFound = True
                Exit For
            End If
        Next ssh
        
    Loop Until SheetFound = False

End Sub


Sub DoForEachNextLoopEDU()
    
    Dim ssh As Object
    Dim sName As String
    Dim SheetFound As Boolean
    
    Do
        sName = InputBox("Enter a state:")
        If Len(sName) = 0 Then
            MsgBox "Canceled or no entry."
            Exit Sub
        End If
        
        SheetFound = False
        For Each ssh In Sheets
            If StrComp(ssh.Name, sName, vbTextCompare) = 0 Then
                SheetFound = True
                Exit For
            End If
        Next ssh
        
        If SheetFound Then
            MsgBox "The sheet name '" & ssh.Name & "' is already taken.", _
                vbCritical
        End If
        
    Loop Until SheetFound = False

    MsgBox "Will use '" & sName & "' as the worksheet name.", vbInformation

End Sub


Sub FullMyWay()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ssh As Object
    Dim sName As String
    
    Do
        sName = InputBox("Enter a state:")
        If Len(sName) = 0 Then
            MsgBox "Canceled or no entry.", vbExclamation
            Exit Sub
        End If
        
        On Error Resume Next
            Set ssh = wb.Sheets(sName)
        On Error GoTo 0
        If ssh Is Nothing Then
            Exit Do
        Else
            MsgBox "The sheet name '" & ssh.Name & "' is already taken.", _
                vbCritical
        End If
    Loop
        
    Dim ErrNum As Long
    On Error Resume Next
        wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = sName
        ErrNum = Err.Number
    On Error GoTo 0
    If ErrNum <> 0 Then
        Application.DisplayAlerts = False
        wb.Sheets(wb.Sheets.Count).Delete
        Application.DisplayAlerts = True
        wsAllStates.Select
        MsgBox "'" & sName & "' is an invalid sheet name.", vbCritical
        Exit Sub
    End If
    
    Dim Data As Variant: Data = Application.Transpose( _
        Array("Headquarters", "Branch Offices", "Sales in 2022"))
    ReDim Preserve Data(1 To 3, 1 To 2)
    
    Data(1, 2) = InputBox("Enter the state's headquarters:")
    Data(2, 2) = InputBox("Enter the amount of brand offices:")
    Data(3, 2) = InputBox("Enter the amount of sales in 2022:")
        
    With wb.Worksheets(wb.Worksheets.Count).Range("A1").Resize(3, 2)
        .Value = Data
        .EntireColumn.AutoFit
    End With

    With wsAllStates ' new name to 'AllStates'
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = sName
        .Select
        .Range("A1").Select
    End With
        
    'wb.Save
        
End Sub

CodePudding user response：

I would do something like this:

Sub test()

    Dim sName, ws As Worksheet, wsAllStates As Worksheet
    Dim sHead, Branches, s2019
    
    '...
    '...
    Do
        sName = InputBox("Enter a state:")
        If Len(sName) = 0 Then Exit Sub 'stop asking if blank
    
        Set ws = Nothing
        On Error Resume Next 'ignore error if no matching sheet
        Set ws = ThisWorkbook.Worksheets(sName)
        On Error GoTo 0      'stop ignoring errors
    Loop While Not ws Is Nothing
    
    If ws Is Nothing Then  'if no existing sheet with that name
    
        'insert state on Allstates page
        'wsAllStates.Range("A1").End(xlDown).Offset(1, 0).Value = sName
        wsAllStates.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = sName 'safer
    
        With ThisWorkbook 'add the new sheet and assign to `ws`
            Set ws = .Worksheets.Add(after:=Worksheets(Worksheets.Count)) 'create a new sheet
        End With
        ws.Name = sName 'and name it
    
        With ws.Range("A1")
            'asks user for information
            sHead = InputBox("Enter the state's headquarters:")
            Branches = InputBox("Enter the amount of brand offices:")
            s2019 = InputBox("Enter the amount of sales in 2022:")
            'places information in appropriate cells with identifiers
            .Offset(0, 0).Value = "Headquarters" 'set value
            .Offset(0, 1) = sHead
            .Offset(1, 0).Value = "Branch offices" 'set value
            .Offset(1, 1) = Branches
            .Offset(2, 0).Value = "Sales in 2022" 'set value
            .Offset(2, 1) = s2019
        End With
    Else
        MsgBox "Sheet '" & sName & "' already exists"
    End If 'new sheet

    'return to Allstates page
    wsAllStates.Activate
    wsAllStates.Range("A1").Select

End Sub