How to find worksheet that contains an error (with 100+ worksheets in it)

[kingbob]

Hi Friendly People of vbaexpress,

Can someone tell me how the following would be possible.

I have a Workbook with a LOT of worksheets in it.

Each work sheet has a value (Lets say the value is 1), In Cell A1.

I want to write a formula that checks every single worksheet to confirm that A1 in each worksheet has the value 1.

If a worksheet does not contain the value 1 in cell A1, is there a way I can have excel tell me exactly WHAT worksheet has the error? (So that i then dont have to go painfully looking at each individual worksheet).

[GTO]

I'm not sure about a regular formula; maybe someone will show us the way. Here is a UDF, but I would think of just running a short bit of code whenever I wanted to check, rather than something volatile. Anyways, try:

In a Standard Module:

Option Explicit
  
Function udfConfirmValues(RequiredVal As Double) As String
Dim wks   As Worksheet
Dim sMsg  As String
  
  Application.Volatile
  
  For Each wks In ThisWorkbook.Worksheets
    If Not wks.Cells(1).Value = RequiredVal Then
      sMsg = sMsg & wks.Name & ", "
    End If
  Next
  
  If Len(sMsg) > 0 Then
    sMsg = Left$(sMsg, Len(sMsg) - 2)
    udfConfirmValues = "Not Compliant: " & sMsg
  Else
    udfConfirmValues = "Good"
  End If
  
End Function

...and in a cell, the formula would be entered: =udfConfirmValues(1)

Does that help?

Mark

[snb]

Sub M_snb()
   for each sh in sheets
     if sh.cells(1)<>1 then c00 =c00 & vblf & sh.name & vbtab & sh.cells(1).value
   next

   msgbox c00
End Sub