Search worksheets and if not found msgbox

[mc136355]

Hi
I have the following code which will search all wksheets for a given name (or part of) and I would like to display message if wksheet does not exist.

However when performing the searching the msgbox will pop up each time to say it does not exist before it has searched all wksheets.

is there code that will display the message after all wksheets have been searched

thanks

my code:

For Each wsFind In Sheets
With wsFind
If .Name Like myArr & "*" Then
MsgBox wsFind.Name & " LIKE " & myArr
Find_Sheet = True
Exit For

Else

MsgBox "DOES NOT EXIST"


End If

End With
Next

 

[cerfani]

use a boolean then to tell you what happened previously...

Dim a As Boolean 'a is declared and set to False
For ...
    If sheet.Name = "whatever" Then
        a = True 'you found it
    End If
Next

MsgBox Iif(a,"Sheet whatever was present", "Not Present")

 

[Fluff]

Maybe

For Each wsFind In Sheets
   With wsFind
      If .Name Like myarr & "*" Then
         MsgBox .Name & " LIKE " & myarr
         Find_Sheet = True
         Exit For
      End If
      If Not Find_Sheet Then MsgBox "DOES NOT EXIST"
   End With
Next

 

[mc136355]

Hi thanks for the quick reply.

However when my code search through the wksheets for example searches for cat and the first sheet is dog then the above code states cat does not exist when it does. I need to be able to search throughout sheets then make a decision after search

thanks again

 

[cerfani]

your code just needs to be formatted so you only call the message box once and after the loop (my code)... you can try Fluff's way too, ive actually never used the Like keyword but i assume his does the same, never even knew the Like keyword existed lol

 

[mc136355]

I apologise cerfani your code actually works for my needs thanks for your input

 

[cerfani]

no prob!