Find Sheet

[gerald24]

Hi Guys,

Could you please check what's missing? I want to find sheet even though part of its name only is being searched.

Sub FindWS()
    Dim strWSName As String
    
    strWSName = InputBox("Enter the sheet name to serach for")
    If strWSName = vbNullString Then
        Exit Sub
    End If
    
    If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        'look if it at least contains part of the name
        Dim s As Worksheet
        For Each s In ThisWorkbook.Sheets
            If InStr(s.Name, strWSName) > 0 Then
                s.Activate
                Exit Sub
            End If
        Next s
        MsgBox "That sheet name does not exist!"
    End If
    
End Sub

Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

Thaaaaaaanks

 

[Momentman]

Maybe use this instead

If InStr(1, s.Name, strWSName, vbTextCompare) > 0

 

[gerald24]

Tried that too, it still cannot find it. Did you try testing it, sir?

 

[Momentman]

Tried that too, it still cannot find it. Did you try testing it, sir?

Yes I did and it worked....

 

[gerald24]

Could you send your complete codes?

Yes I did and it worked....

 

[Momentman]

Not different from what you initially sent

Sub FindWS()
    Dim strWSName As String
    
    strWSName = InputBox("Enter the sheet name to serach for")
    If strWSName = vbNullString Then
        Exit Sub
    End If
    
    If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        'look if it at least contains part of the name
        Dim s As Worksheet
        For Each s In ThisWorkbook.Sheets
            If InStr(1, s.Name, strWSName, vbTextCompare) > 0 Then
                s.Activate
                Exit Sub
            End If
        Next s
        MsgBox "That sheet name does not exist!"
    End If
    
End Sub
Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

 

[gerald24]

again I tried using your code. so in a new workbook there are sheet1, sheet2, and sheet 3

I tried searching for "2" or "3" but to no avail
I am using Excel 2010, does that have an effect?

Not different from what you initially sent

Sub FindWS()
    Dim strWSName As String
    
    strWSName = InputBox("Enter the sheet name to serach for")
    If strWSName = vbNullString Then
        Exit Sub
    End If
    
    If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        'look if it at least contains part of the name
        Dim s As Worksheet
        For Each s In ThisWorkbook.Sheets
            If InStr(1, s.Name, strWSName, vbTextCompare) > 0 Then
                s.Activate
                Exit Sub
            End If
        Next s
        MsgBox "That sheet name does not exist!"
    End If
    
End Sub
Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

 

[lucky7777]

Not different from what you initially sent

Sub FindWS()
    Dim strWSName As String
   
    strWSName = InputBox("Enter the sheet name to serach for")
    If strWSName = vbNullString Then
        Exit Sub
    End If
   
    If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        'look if it at least contains part of the name
        Dim s As Worksheet
        For Each s In ThisWorkbook.Sheets
            If InStr(1, s.Name, strWSName, vbTextCompare) > 0 Then
                s.Activate
                Exit Sub
            End If
        Next s
        MsgBox "That sheet name does not exist!"
    End If
   
End Sub
Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

Hi Momentman / fellow members,

I'm new to this forum and the info here really helped me alot in my current VBA module. I'm reaching out because i'm caught in a bottleneck right now which i have no solution for.


Would you / anyone happen to know how i can exit sub if the sheet doesn't exist? I used the original code that was posted here but i changed the input portion and instead, indicated the string name i needed to search for.

I'm trying to do a scenario where if any of the worksheets contain the text "rawfile", another sub procedure will be called upon. Otherwise, if none of the worksheets contain the text "rawfile", i'll exit the Sub Activate_TempRawFile().

My current syntax exits the sub immediately and I'm not sure how should i write the Else statement for my desired scenario to happen.

Your help will be greatly appreciated! Thanks in advance!


-----------------------------------------------------------


Sub Activate_TempRawFile()

Dim strWSName As String
Dim ws As Worksheet
Dim s As Worksheet


strWSName = "rawfile"

MsgBox "The name of the active workbook is " & ActiveWorkbook.Name

For Each ws In ActiveWorkbook.worksheets
ws.Name = UCase(ws.Name)

Next


If SheetExists(strWSName) Then
worksheets(strWSName).Activate





Else
'look if it at least contains part of the name
For Each s In ThisWorkbook.Sheets
If InStr(1, s.Name, strWSName, vbTextCompare) > 0 Then
s.Activate
Exit For

Else: Exit Sub
MsgBox "Worksheet is not found. The macro will now end."

End If

Next s
MsgBox "That sheet name does not exist!"

End If


Call FormatDate_Column_A

End Sub


------------------------------------------------------------
Function SheetExists(strWSName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = worksheets(strWSName)
If Not ws Is Nothing Then SheetExists = True
End Function

 

[Norie]

You shouldn't really be using the function SheetExists as it's designed to find a sheet with an exact, specific name.

Do you want to run the same code on every worksheet that contains 'rawfile' in the name?

 

[lucky7777]

You shouldn't really be using the function SheetExists as it's designed to find a sheet with an exact, specific name.

Do you want to run the same code on every worksheet that contains 'rawfile' in the name?

Hi Norie,

Thanks for your tip and noted on the portion regarding SheetExists.

The objective of this code is to search within the active workbook to see if any of the worksheets contain the text "rawfile". If there's such a worksheet, another sub procedure will be called upon.

Otherwise, if none of the worksheets in the workbook contain the text "rawfile", i'll wish to exit the Sub Activate_TempRawFile().

 

[Norie]

Could you start a new thread for your question?

Include a link back to this thread for reference.