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