For Each worksheet with If statement

[leatherhen99]

Good Day,

I have read through so many posts and I'm not finding the issue that I'm struggling to solve.

I am trying to include logic where my macro will stop if there is any missing data... The user, prior to running the macro, needs to move a sheet into the macro-enabled template. The sheet name is in the following format: "LockedMeterStateYYYYMMDDHHMMSS"... So I can't tell it to look for a specific name. I'm able to get this code to work to activate the sheet:

For Each ws In Worksheets
        If InStr(1, ws.Name, "LockedMeterState202", vbTextCompare) Then
            ws.Activate
            End If
    Next

But if the sheet's not there... I want to alert the user to add the sheet so it doesn't wreak havoc on the final output...

What I'd like to be able to do is add an ELSE statement so that if that sheet doesn't exist, it will give a messagebox stating the tab isn't there, add the sheet, and restart the macro... So I've used other threads to create this:

For Each ws In Worksheets
        If InStr(1, ws.Name, "LockedMeterState202", vbTextCompare) Then
            ws.Activate
        Else
                MsgBox "The daily Locked Meter State tab is not in workbook. Please update the file and restart the macro."
            Exit Sub
            End If
Next

Unfortunately, it doesn't work This code always runs straight thru to the Else statement and exits the sub... it doesn't check anything but the current sheet. I'm certainly not saying it's not a user error, but I've been pondering over this for a few days and I'm at a loss. Any help would be greatly appreciated! Thanks again!

 

[Joe4]

If you are looping through ALL the sheets in your workbook, and checking each one individually, you do NOT want it to exit the sub the first one that doesn't match (i.e. what if this sheet isn't the first one in your workbook). And once you found it, you want to exit your loop.

I think you will want to add a flag to indicate if the sheet is found at all, i.e.

Dim ws as Worksheet
Dim fnd as Boolean

'Initialize fnd
fnd = False

For Each ws In Worksheets
    If InStr(1, ws.Name, "LockedMeterState202", vbTextCompare) Then
        fnd = True           
        ws.Activate
        Exit For
    End If
Next ws

'Check to see if sheet was found
If fnd = False Then
    MsgBox "The daily Locked Meter State tab is not in workbook. Please update the file and restart the macro."
    Exit Sub
End If

 

[Fluff]

How about

   Dim Flg As Boolean
   For Each ws In Worksheets
      If InStr(1, ws.Name, "LockedMeterState202", vbTextCompare) Then
         ws.Activate
         Flg = True
         Exit For
      End If
   Next
   If Flg = False Then
      MsgBox "The daily Locked Meter State tab is not in workbook. Please update the file and restart the macro."
      Exit Sub
   End If

 

[leatherhen99]

YOU. ARE. A. GENIUS!!!!!!!!! I tried several methods but I couldn't quite figure out the flag feature, but you made it look so easy!!!! Thank you so much! This is EXACTLY what I needed!!!!

If you are looping through ALL the sheets in your workbook, and checking each one individually, you do NOT want it to exit the sub the first one that doesn't match (i.e. what if this sheet isn't the first one in your workbook). And once you found it, you want to exit your loop.

I think you will want to add a flag to indicate if the sheet is found at all, i.e.

Dim ws as Worksheet
Dim fnd as Boolean

'Initialize fnd
fnd = False

For Each ws In Worksheets
    If InStr(1, ws.Name, "LockedMeterState202", vbTextCompare) Then
        fnd = True          
        ws.Activate
        Exit For
    End If
Next ws

'Check to see if sheet was found
If fnd = False Then
    MsgBox "The daily Locked Meter State tab is not in workbook. Please update the file and restart the macro."
    Exit Sub
End If

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

YOU. ARE. A. GENIUS!!!!!!!!! I tried several methods but I couldn't quite figure out the flag feature, but you made it look so easy!!!! Thank you so much! This is EXACTLY what I needed!!!!

You are welcome.
Glad we were able to help.

These things can get a little tricky, especially when loops are involved.
In these situations, sometimes it is helpful to step into the code, and go through it one line at a time (using the F8 key) to see what it happening each step of the way.
Many times, it becomes apparent what the issue is when you see what the code is doing.