Macro Issues - Search for call value in a range

[HNoble]

Hi All

Can someone please help with my issue. Basically I have a workbook that has a Template tab, Home Page tab, Reports tab plus mutiple project tabs with different names.

On the Home Page tab I have a search box where I will enter a project tab name to search it in the open tabs. The code is working but only when I type out the value as shown in the range on the project tab. Is there a way of searching the range and bringing back anything that is contained? Or is there a different way completely.

Here is my code:


Dim ws As Worksheet, WRD As String, loc As Range

WRD = Sheets("Home Page").Range("F27").Value
For Each ws In Worksheets
ws.Visible = True
Next

For Each ws In Worksheets
If ws.Name <> "Template" And ws.Name <> "Home Page" And ws.Name <> "Reports" Then ws.Visible = xlSheetVisible
With ws
Set loc = .Range("C1:E8").Find(What:=WRD, SearchDirection:=xlNext)
If loc Is Nothing Then ws.Visible = xlSheetHidden

End With
Next ws


Any help is appreciated. Thanks!
Hannah

 

[Yongle]

Please clarify based on this example ..

3 of the project tab names are :
Mouse
Dormouse
Mousehole

user enters mouse into F27 and runs the macro ..

What should happen next ?
- hide all sheets except those 3
- something different ?

 

[DanteAmor]

Try this

Sub test()
  Dim ws As Worksheet, WRD As String, loc As Range
  Application.ScreenUpdating = False
  WRD = Sheets("Home Page").Range("F27").Value
  For Each ws In Worksheets
    Select Case ws.Name
      Case "Template", "Home Page", "Reports"
      Case Else
        Set loc = ws.Range("C1:E8").Find(What:=WRD, LookIn:=xlValues,[COLOR=#0000ff] lookat:=xlPart[/COLOR])
        If Not loc Is Nothing Then
          ws.Visible = xlSheetVisible
        Else
          ws.Visible = xlSheetHidden
        End If
      End Select
  Next ws
End Sub

 

[HNoble]

That's worked perfectly DanteAmor, thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.