how search and select a worksheet

[albasel]

Good day every body

I have written a code to search about a worksheet and select it, but when i run the macro it show me the msgbox and when i enter the worksheet name , i press OK and then nothing happens.

Could any body help me in that???

Thanks in advance.

My code is the following:

Sub Macro2()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim sheetName As String
Dim shn As String
Dim i As Integer

sheetName = InputBox("Please Enter the sheet name")

On Error Resume Next
Set ws = Worksheets(sheetName)

For i = 1 To i <= Worksheets.Count
Set ws2 = Worksheets(i)
shn = ws2.Name

If InStr(shn, sheetName) <> 0 Then

Sheets(sheetName).Select

Range("A1").Select
Else
MsgBox ("Sorry, the sheet name " + sheetName + " not found")
End If


Next i

End Sub

 

[Andrew Poulsom]

Try changing:

For i = 1 To i <= Worksheets.Count

to:

For i = 1 To Worksheets.Count

 

[albasel]

Than you Andrew... you are right

But I still have a problem which is the for loop will not stop even we found the worksheet.

So, how can we stop the loop if we found the worksheet??

Your cooperation is highly appreciated.

 

[Andrew Poulsom]

Try:

Sub Macro2()
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim sheetName As String
    Dim shn As String
    Dim i As Integer
    sheetName = InputBox("Please Enter the sheet name")
    On Error Resume Next
    Set ws = Worksheets(sheetName)
    For i = 1 To Worksheets.Count
        Set ws2 = Worksheets(i)
        shn = ws2.Name
        If InStr(shn, sheetName) <> 0 Then
            Sheets(sheetName).Select
            Range("A1").Select
            Exit Sub
        End If
    Next i
    MsgBox ("Sorry, the sheet name " + sheetName + " not found")
End Sub

 

[albasel]

Thanks Andrew

My code now is as the following

Sub Macro2()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim sheetName As String
Dim shn As String
Dim i As Integer

sheetName = InputBox("Please Enter the sheet name")

On Error Resume Next
Set ws = Worksheets(sheetName)

For i = 1 To Worksheets.Count
Set ws2 = Worksheets(i)
shn = ws2.Name

If InStr(shn, sheetName) <> 0 Then

Sheets(sheetName).Select

Range("A1").Select

Exit Sub

End If

Next i

MsgBox ("Sorry, the sheet name " + sheetName + " not found")

End Sub

The result when I run this macro is selecting the current worksheet not the searched one.

Please help to select the searched worksheet and activate it.

Thanks

 

[Peter_SSs]

The result when I run this macro is selecting the current worksheet not the searched one.

Please help to select the searched worksheet and activate it.

A few things ..

1. You are using code tags when posting your code which is good, but your code isn't indented to start with. Reading and de-bugging un-indented code is much more difficult so please indent future code. More people will help.

2. Using 'On Error Resume Next' in your code and not turning that off again as soon as possible is dangerous. It often means errors that really should show up don't.

3. You problem, I suspect, is that you are searching for a partial sheet name. If you search for a complete sheet name that code worked for me.

Here is some suggested modified code. I have just commented the old lines of your code that I don't think are needed so you can see what I have changed. If the code does what you want those lines could be removed completely to tidy up a bit.

Sub SelectSheet()
    'Dim ws As Worksheet
    'Dim ws2 As Worksheet
    Dim sheetName As String
    Dim shn As String
    Dim i As Integer
    
    sheetName = InputBox("Please Enter the sheet name")
    
'    On Error Resume Next
'    Set ws = Worksheets(sheetName)
    
    For i = 1 To Worksheets.Count
'        Set ws2 = Worksheets(i)
        shn = Worksheets(i).Name
        
        If InStr(shn, sheetName) <> 0 Then
        
'            Sheets(sheetName).Select
            Sheets(i).Select '<-- New line
            
            Range("A1").Select
            
            Exit Sub
        
        End If
    
    Next i
    
    MsgBox ("Sorry, the sheet name containing " + sheetName + " not found")

End Sub

 

[rjwebgraphix]

I've personally found exiting the for statement a more practicle usage.

Using Peter's example with a few changes.

Sub SelectSheet()
    Dim sheetName As String
    Dim shn As String
    Dim i As Integer
    Dim shtfound As Boolean
    
    sheetName = InputBox("Please Enter the sheet name")
    
    shtfound = False
    For i = 1 To Worksheets.count
        shn = Worksheets(i).Name
        If InStr(shn, sheetName) <> 0 Then
            
            Sheets(i).Select
            Range("A1").Select
            shtfound = True
            Exit For
        End If
    Next i
    If shtfound = False Then
        Msgbox ("Sorry, the sheet name containing " + sheetName + " not found")
    End If
End Sub

 

[Peter_SSs]

I've personally found exiting the for statement a more practicle usage.

I actually find exiting any loop prematurely untidy and did so in my previous code only because the OP had used that structure.

Also, particularly because the user is entering the sheet name (or part of it) there is potential problem with UPPER/lower case text being entered in the Input Box.

So, if I was starting from scratch, I would use code like this.

Sub SelectSheet()
    Dim sheetName As String, UCShName As String
    Dim i As Integer
    Dim shtfound As Boolean

    sheetName = InputBox("Please Enter the sheet name")
    UCShName = UCase(sheetName)
    
    Do
        i = i + 1
        shtfound = InStr(UCase(Worksheets(i).Name), UCShName) > 0
    Loop While Not shtfound And i < Worksheets.Count
    
    If shtfound Then
        Sheets(i).Select
        Range("A1").Select
    Else
        MsgBox ("Sorry, the sheet name containing " _
            & sheetName & " not found")
    End If
End Sub

 

[rjwebgraphix]

So, if I was starting from scratch, I would use code like this.

and that's why you get paid the big bucks!

I should probably revisit the way I do a lot of things. I am prematurely exiting loops all the time.

 

[Peter_SSs]

and that's why you get paid the big bucks!

I wish!!