CODEWITHSUNDEEP
excelvba
Dan Avidan
Dan Avidan

Reputation: 19

search worksheets

I have this VBA code, searching sheets name, the code works.

How can I change it to search only partial name? full name dog, partial name do*, something like this...

Dim xName As String
Dim xFound As Boolean

xName = InputBox("הכנס/י שם ספק לחיפוש בקובץ", "Sheet search")

If xName = "" Then Exit Sub

On Error Resume Next
ActiveWorkbook.Sheets(xName).Select
xFound = (Err = 0)
On Error GoTo 0

If xFound Then    
Else
    MsgBox ("הספק " & xName & " לא נמצא")        
End If

Upvotes: 0

Views: 41

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

There's no need (and recommended ) to use On Error Resume Next in this scenario.

Loop through the worksheets, and compare the worksheet's name with the value you are looking for. You can either use the wild-card *, or use Instr function.

Dim xName As String
Dim xFound As Boolean
Dim Sht As Worksheet

xName = InputBox("הכנס/י שם ספק לחיפוש בקובץ", "Sheet search")

For Each Sht In ThisWorkbook.Worksheets
    If Sht.Name Like xName & "*" Then ' sheet name found
        ' do something
        xFound = True ' raise flag

        Exit For
    End If
Next Sht

If xFound = False Then
    MsgBox ("הספק " & xName & " לא נמצא")
End If

Note: try not to use the Hebrew characters while coding in VBA, it will only give you grief in the future (I stopped that 15 years ago).

If you want to use Instr function, the use something like the code bellow:

If InStr(Sht.Name, xName) > 0 Then  ' <-- Using Instr

Upvotes: 3

Related Questions