Find a worksheet by name

[sukumar.vb]

I was looking to find a worksheet by name.

I did this program: -

[vba]Option Base 1
Private Sub test()
Dim n, c As Integer
c = Worksheets.Count


For n = 1 To c
If Worksheets(n).Name = "Work" Then
Worksheets("Work").Activate
Else
MsgBox "Not found", vbInformation
End If
Next n
End Sub
[/vba] I guess that there could be a better one. Please suggest.

Above program will go to first sheet and then perform MsgBox. Then it will go to second sheet and perform Msgbox.
So, total n Msgbox will be displayed, if there are n sheets in a Workbook.
I am looking to display only one Msgbox, if sheet "Work" in not found in n sheets.

[GTO]

If you want to loop through all the sheets to find one, you would want to set a flag to True if it was found. Then the MsgBox would be called after the looping and if the flag was not True.

Rather than looping however, maybe try setting a reference to the sheet. If the Set fails, we know the sheet doesn't exist.
[VBA]Option Explicit

Sub TestIt()
Dim mySheet As Worksheet
Dim strShName As String

strShName = "Work"

If SheetExists(strShName, mySheet) Then
mySheet.Activate
Else
MsgBox "The worksheet named: " & strShName & " does not exist.", vbInformation, vbNullString
End If
End Sub

Function SheetExists(ByVal ShName As String, ByRef wks As Worksheet, Optional ByVal WB As Workbook) As Boolean

'// If the arg is not passed, assume we are checking ThisWorkbook //
If WB Is Nothing Then Set WB = ThisWorkbook
'// In case wks was already set by the calling procedure. //
Set wks = Nothing

'// Allow an error to be passed by, just for the one line. //
On Error Resume Next
'// If the sheet (as referred to by its tab) doesn't exist, wkk will//
'// continue to be Nothing. //
Set wks = WB.Worksheets(ShName)
On Error GoTo 0
If Not wks Is Nothing Then SheetExists = True
End Function[/VBA]

Hope that helps,

Mark

[svarma]

Here is a slight variation on your original code that accomplishes the task:

[vba]Private Sub test()
Dim n As Integer, c As Integer
c = Worksheets.Count
Dim found As Boolean
found = False

For n = 1 To c
If Worksheets(n).Name = "Work" Then
found = True
Worksheets("Work").Activate
End If
Next n
If found = False Then MsgBox "Not found", vbInformation
End Sub[/vba]

[Paul_Hossler]

More along the same lines. Test 3 will take some corrective actions (as example)

[vba]
Sub Test_2()
Dim iDummy As Long

iDummy = 0
On Error Resume Next
iDummy = Worksheets("Work").Index
On Error GoTo 0

If iDummy > 0 Then
Worksheets("Work").Activate
Else
msgbox "No worksheet"
End If

End Sub


Sub Test_3()

On Error GoTo NoWorksheet
Worksheets("Work").Activate

msgbox "Sub continues"

Exit Sub

NoWorksheet:
If Err.Number = 9 Then
msgbox "No worksheet, adding one for you"
Worksheets.Add.Name = "Work"
Else
msgbox "Some other error = " & Err.Number & " (" & Err.Description & ")"
End If

Resume Next
End Sub
[/vba]

Paul