Need debug help

[pzamory]

Keep getting an error message when running this macro. Looking for some expertise. Many thanks. I added all the tab names in the Array field. I can do a search but then it crashes with an error message.



Option Explicit

Sub FindMeSheet()

Dim CheckNum$
Dim varSheets As Variant
Dim i As Long
Dim c As Range

CheckNum = InputBox("Enter Search String", "Spooky2 Co-Pilot")
If CheckNum = vbNullString Then Exit Sub

varSheets = Array("Vitamins", "QA", "Testimonials", "Natural", "Essential", "Eating", "Audio", "Tutorials")

For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))

Set c = .UsedRange.Find(What:=CheckNum, LookIn:=xlValues)

If Not c Is Nothing Then
MsgBox c.Address & " On " & Sheets(varSheets(i)).Name
End If

End With
Next
End Sub

 

[johnnyL]

The code works on my end, what line of code gives you an error?

 

[6StringJazzer]

What is the error message?
What line of code does the error occur on?

When I run this code on a test file it works and does not cause an error.

By the way if you have all the tab names in the array, you could more easily and reliably do this:

Sub FindMeSheet()

   Dim CheckNum As String
   Dim WS As Worksheet
   Dim c As Range
   
   CheckNum = InputBox("Enter Search String", "Spooky2 Co-Pilot")
   If CheckNum = vbNullString Then Exit Sub
   
   For Each WS In Worksheets
      With WS
   
         Set c = .UsedRange.Find(What:=CheckNum, LookIn:=xlValues)
         
         If Not c Is Nothing Then
            MsgBox c.Address & " On " & .Name
         End If
      
      End With
   Next
   
End Sub

 

[pzamory]

What is the error message?
What line of code does the error occur on?

When I run this code on a test file it works and does not cause an error.

By the way if you have all the tab names in the array, you could more easily and reliably do this:

Sub FindMeSheet()

   Dim CheckNum As String
   Dim WS As Worksheet
   Dim c As Range
  
   CheckNum = InputBox("Enter Search String", "Spooky2 Co-Pilot")
   If CheckNum = vbNullString Then Exit Sub
  
   For Each WS In Worksheets
      With WS
  
         Set c = .UsedRange.Find(What:=CheckNum, LookIn:=xlValues)
        
         If Not c Is Nothing Then
            MsgBox c.Address & " On " & .Name
         End If
     
      End With
   Next
  
End Sub

Thanks for the quick help. I believe I found the problem. I named one of the tabs "Essential Oils". I took out the "Oils" and now it seems to work. Best wishes for a happy holiday season and New Year.

 

[6StringJazzer]

My code would have prevented this problem. If you type out a list of tab names in your code, they have to be perfect matches for the actual tab names. If you just loop through all the tabs, you don't have to type in the names, and the code does not have to be changed if you add, delete, or rename tabs.