VBA / sheetname contains "Check_**" ("Check_BC", "Check_BD", "Check_BB"...)

Question

I am the beginner of VBA. So there are several sheets called "Check_BC", "Check_BD", "Check_BB"... among the other sheets.

If there is TRUE value in those sheets, Range("C5:DD90"), write "PASS" in "Summary" sheet. Otherwise there is at least one FALSE value, write "FAIL".

I got stuck in choosing sheets which have named "Check_**"

The error line is checksh = Worksheets("sh") and the error is "Subscript out of range" Hope I'm not doing stupid. Thanks.

Sub findtrial()

 Dim c As Range
 Dim checkrange As Range
 Dim ws_1 As Worksheet
 Dim i As Integer
 Dim checksh As Worksheet
 
 i = Sheets.Count
 sh = Sheets(i).Name = "Check_**"
 checksh = Worksheets("sh")
 
 Set ws_1 = Worksheets("Summary")
 
 Do While i > 0
 
 If Sheets(i).Name = "Check_**" Then
 
 Set checkrange = checksh.Range("C5:DD90")

  With checkrange

     Set c = .Find(False, Lookat:=xlWhole)
         If Not c Is Nothing Then
            ws_1.Range("C2").Select
            Selection.Interior.ColorIndex = "3"
            Selection.Value = "FAIL"
         Else
            ws_1.Range("C2").Select
            Selection.Interior.ColorIndex = "43"
            Selection.Value = "PASS"
            
         End If
            
   End With
  
 End If
 i = i - 1
 Loop
 
End Sub

when the false value comes out

FAIL should be in the Summary tab

all the tabs in workbook

Answer 1

Check If No Occurrence of False in Multiple Worksheets

Not Checking "Summary"...
Checking "Check_df"...
Checking "check_DD"...

The Code

Sub CheckFailPass()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet, HasFailed As Boolean
    
    For Each ws In wb.Worksheets
        If LCase(ws.Name) Like "check_??" Then ' it's a match; check it
            Debug.Print "Checking """ & ws.Name & """..."
            With ws.Range("C5:DD98")
                If Not .Find(False, , xlValues, xlWhole) Is Nothing Then
                    HasFailed = True
                    Exit For ' one fail is enough: stop checking
                End If
            End With
        Else ' it's not a match; do nothing (don't check this worksheet)
            Debug.Print "Not Checking """ & ws.Name & """..."
        End If
    Next ws
   
    With wb.Sheets("Summary").Range("C2")
        If HasFailed Then
            .Value = "FAIL"
            .Interior.ColorIndex = "3"
        Else
            .Value = "PASS"
            .Interior.ColorIndex = "43"
        End If
    End With
 
End Sub

Some Thoughts

• There are many details in your code so it's hard to believe that you would run it on multiple workbooks. Therefore specify the workbook explicitly:

  • For the workbook containing this code use (should be most often):

    Set wb = ThisWorkbook
    

  • For another workbook, specify it like this (also specify the file extension):

    Set wb = Workbooks("Test.xlsx")
    

  • If the workbook is closed, to open it (best specify the complete path), use:

    Set wb = Workbooks.Open("C:\Test\Test.xlsx")
    

  • If you plan on running the code from an add-in or from PERSONAL.xlsb using it on a workbook that is active at the moment, use:

    Set wb = ActiveWorkbook
    

• The Sheets collection consists of all the worksheets and charts in the workbook. In your code, you are reading from worksheets so use the Worksheets collection. Since you are not interested in the number of worksheets (you need to check each if it is named after the string pattern), it is far more readable to use a For Each...Next loop.

  For Each ws in wb.Worksheets ' not 'wb.Sheets'!
  Next ws 
  

• In your particular case, you can use the Like operator to compare the worksheet names with the string pattern. Note that the comparison is case-sensitive (A<>a) so you can use the LCase function to return the worksheet name in lowercase and compare it with the lowercase pattern to make the comparison case-insensitive. The pattern "check_??" means that the string starts with "check_" and ends with two additional characters.

  If LCase(ws.Name) Like "check_??" Then
  

• Using Debug.Print is a very useful way to monitor what is going on in the code.

  Debug.Print "Checking """ & ws.Name & """..."
  Debug.Print "Not Checking """ & ws.Name & """..."
  

• By using the LookIn argument's xlValues parameter, you are ensuring the Find method will find False as the result of a formula. Note that the Find method with xlValues will fail if rows or columns are hidden and if the worksheet is filtered.

  .Find(False, , xlValues, xlWhole)
  

• Once you have hit a match, you can use a Boolean flag and exit the loop.

  HasFailed = True
  Exit For
  

• Using the flag (True or False), you will construct the final If statement.

  If HasFailed Then
  Else
  End If
  

• Selecting and activating slows down the code so it is to be avoided whenever possible.