Counting Errors

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

Faraz A. Qureshi

Kindly clarify with an example like finding how many of cells in A1:A10 do
not reflect the names of sheets present in a workbook?

Best Regards,

Faraz
 
Do you mean

Sub Macro()
strFormula = "=SUM(IF(A1:A10<>"""",IF(ISNA(MATCH(A1:A10,B1:B20,0)),1,0)))"
MsgBox Application.Evaluate(strFormula)
End Sub

If this post helps click Yes
 
No Jacob this is a totally different question pertaining as to how to count
errors in a loop like:

For Each cell In Application.InputBox("Select", , , , , , , 8)
If a cell is not containing sheet name add to counter
End if
Carry on
.... Next
 
Try the below macro,.....

Sub Macro()
For Each cell In Application.InputBox("Select", , , , , , , 8)
On Error Resume Next
Set sh = Sheets(CStr(cell.Text))
If sh Is Nothing Then intCount = intCount + 1
Next
End Sub



''The best way is to use a function such as below to check whether the sheet
exists or not and then to count it

Function SheetExists(strSheet As String) As Boolean
Dim sh As Worksheet
On Error Resume Next
Set sh = Sheets(strSheet)
If Not sh Is Nothing Then SheetExists = True
End Function

If this post helps click Yes
 
If you don't set sh back to nothing after the If statement then it retains
its value and the If statement will not count any after the first valid
setting.

Sub Macro()
For Each cell In Application.InputBox("Select", , , , , , , 8)
On Error Resume Next
Set sh = Sheets(CStr(cell.Text))
If sh Is Nothing Then intCount = intCount + 1

set sh = nothing

Next
End Sub
 
Sorry Jacob but the code:

Sub Macro()
Dim intCounter As Long
intCounter = 0
For Each cell In Application.InputBox("Select", , , , , , , 8)
On Error Resume Next
Set sh = Sheets(CStr(cell.Text))
If sh Is Nothing Then intCounter = intCounter + 1
Next
MsgBox intCounter
End Sub

doesn't seem to be presenting correct number of errors. It seems to be
missing sometimes the blank cells.
 
Have you gone through the previous posts..Try the below..

Sub Macro()
Dim intCounter As Long
intCounter = 0
For Each cell In Application.InputBox("Select", , , , , , , 8)
On Error Resume Next
Set sh = Sheets(CStr(cell.Text))
If sh Is Nothing Then intCounter = intCounter + 1
Set sh = Nothing
Next
MsgBox intCounter
End Sub

If this post helps click Yes
 
Back
Top