ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Counting Errors (https://www.excelbanter.com/excel-programming/431849-counting-errors.html)

Faraz A. Qureshi
Counting Errors
 
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

Jacob Skaria
Counting Errors
 

--
If this post helps click Yes
---------------
Jacob Skaria
--"names of sheets" OR names in sheets..
--Are you expecting a macro?

"Faraz A. Qureshi" wrote:

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

Jacob Skaria
Counting Errors
 
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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

Faraz A. Qureshi
Counting Errors
 
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

--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

Jacob Skaria
Counting Errors
 
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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

OssieMac
Counting Errors
 
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


--
Regards,

OssieMac


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

Jacob Skaria
Counting Errors
 
Thanks "OssieMac" for pointing that out..

If this post helps click Yes
---------------
Jacob Skaria


"OssieMac" wrote:

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


--
Regards,

OssieMac


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

Faraz A. Qureshi
Counting Errors
 
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.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

Faraz A. Qureshi
Counting Errors
 
RESOLVED!!!
Thanx to OssieMac!!!!
--
Do check "Yes" if this post is helpful,
Best Regards,

Faraz


"Faraz A. Qureshi" wrote:

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.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

Jacob Skaria
Counting Errors
 
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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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

--
Best Regards,

Faraz


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

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


All times are GMT +1. The time now is 08:28 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com