For Each Next together with If Else

[someone21]

I want to know if a cell contains the 1) existing worksheet name 2) is empty.

If both conditions are NOT satisfied(meaning the cell is not empty and contains a unique worksheet name), then an action will take place.

Here is my code which contains some errors

Dim ws As Worksheet
For Each ws In Worksheets
If Range("a1").Value = ws.Name Then
MsgBox "exists already"
End If
Next ws


If IsEmpty(Range("a1")) Then
 MsgBox "cell empty"

Else


'  action statement here 

End If

Please look at the code and tell the mistake

 

[HalfAce]

Here's how you can make the code you posted work.

Dim ws As Worksheet
For Each ws In Worksheets
  If IsEmpty(ws.Range("a1")) Then
    MsgBox "cell empty"
  Else
    If ws.Range("a1").Value = ws.Name Then MsgBox "exists already"
End If
Next ws

Here's another way to achieve the same thing using a select case statement instead of If statements.

Dim ws As Worksheet
For Each ws In Worksheets
  Select Case ws.Range("A1").Value
    Case ""
      MsgBox "cell empty"
    Case ws.Name
      MsgBox "exists already"
  End Select
Next ws

Either one should do what you've asked for.
Hope it helps.

 

[someone21]

Thanks, but I think I did not explain it well,

Now from the above 2 situations, there is a 3rd part that if the cell does not classify both(not one of them) situation, which implies that the cell contains a unique worksheet name and not empty. So how to add this 3rd situation with the above 2 conditions....

 

[AlphaFrog]

Try this...

    Dim ws     As Worksheet
    For Each ws In Worksheets
        If ws.Range("A1").Value = ws.Name Then
            MsgBox "exists already"
        ElseIf IsEmpty(ws.Range("A1")) Then
            MsgBox "cell empty"
        Else
            MsgBox "Doesn't exist and not empty."
        End If
    Next ws

Or this...

    Dim ws     As Worksheet
    For Each ws In Worksheets
        Select Case ws.Range("A1").Value
          Case ""
            MsgBox "cell empty"
          Case ws.Name
            MsgBox "exists already"
          Case Else
            MsgBox "Doesn't exist and not empty."
        End Select
    Next ws

 

[someone21]

Does not work. Here is the code. Again my aim is to make sure the cell does not contain existing worksheet name AND is not empty, only then will the action of creating a new worksheet take place. So if any of the two conditions take place, there should be message box and only if otherwise the action of creating new worksheet takes place.

Dim ws     As Worksheet
    For Each ws In Worksheets
        If ws.Range("A1").Value = ws.Name Then
            MsgBox "exists already"
        ElseIf IsEmpty(ws.Range("A1")) Then
            MsgBox "cell empty"
        Else
            Sheets("Template").Copy After:=Sheets("Template")
ActiveSheet.Name = Sheets("Index").Range("a1") ' to create worksheet based on cell name and it stops working here

Call xyz   ' another marco
        End If
    Next ws

 

[AlphaFrog]

... my aim is to make sure the cell does not contain existing worksheet name AND is not empty, only then will the action of creating a new worksheet take place.

    Dim ws As Worksheet
    
    If Range("A1").Value = "" Then
        MsgBox "Cell ""A1"" is empty.", vbInformation, "No Sheet Name"
    Else
        On Error Resume Next
            Set ws = Sheets(Range("A1").Value)
        On Error GoTo 0
        If Not ws Is Nothing Then
            MsgBox "Worksheet '" & ws.Name & "' already exists."
        Else
            Sheets("Template").Copy After:=Sheets("Template")
            ActiveSheet.Name = Sheets("Index").Range("A1") ' to create worksheet based on cell name and it stops working here
            Call xyz   ' another marco
        End If
    End If