count Worksheet using Wildcard
 

hi all,

find following my Sub:

Sub countTestCases()
Dim Sht As Object
Dim nr As Integer
nr = 0
For Each Sht In ActiveWorkbook.Sheets
If Sht.name = "TestCases*" Then nr = nr + 1
Next
MsgBox nr
End Sub

this is simply to count how many Worksheets starting with the name
"TestCases" are in the current Workbook. for example there might be
"TestCases0",
"TestCases5" and
"TestCases14",
if i execute above routine all i end up with is 0 for nr (but my goal
is it to display 3).

i assumed that by using the wildcard character "*" at the end of the
name "TestCases", this would count for it - either i am wrong with my
assumption or i implemeted incorrectly.

if any of you could point out what i am doing wrong or maybe there is
a more elegant way of writing above code, this would be mostly
appreciated.

cheers....


....jurgenC!

count Worksheet using Wildcard
 

Hi Jurgen,

How about this

Sub countTestCases()
Dim Sht As Object
Dim nr As Integer
nr = 0
For Each Sht In ActiveWorkbook.Sheets
If InStr(Sht.Name, "TestCases") 0 Then nr = nr + 1
Next
MsgBox nr
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jC!" wrote in message
om...
hi all,

find following my Sub:

Sub countTestCases()
Dim Sht As Object
Dim nr As Integer
nr = 0
For Each Sht In ActiveWorkbook.Sheets
If Sht.name = "TestCases*" Then nr = nr + 1
Next
MsgBox nr
End Sub

this is simply to count how many Worksheets starting with the name
"TestCases" are in the current Workbook. for example there might be
"TestCases0",
"TestCases5" and
"TestCases14",
if i execute above routine all i end up with is 0 for nr (but my goal
is it to display 3).

i assumed that by using the wildcard character "*" at the end of the
name "TestCases", this would count for it - either i am wrong with my
assumption or i implemeted incorrectly.

if any of you could point out what i am doing wrong or maybe there is
a more elegant way of writing above code, this would be mostly
appreciated.

cheers....


...jurgenC!

count Worksheet using Wildcard
 

hi,
instead of If Sht.name = "TestCases*" Then nr = nr + 1
use If Left$(Sht.name,9) = "TestCases*" Then nr = nr + 1

hth
Heiko

(jC!) wrote:

hi all,

find following my Sub:

Sub countTestCases()
Dim Sht As Object
Dim nr As Integer
nr = 0
For Each Sht In ActiveWorkbook.Sheets
If Sht.name = "TestCases*" Then nr = nr + 1
Next
MsgBox nr
End Sub

this is simply to count how many Worksheets starting with the name
"TestCases" are in the current Workbook. for example there might be
"TestCases0",
"TestCases5" and
"TestCases14",
if i execute above routine all i end up with is 0 for nr (but my goal
is it to display 3).

i assumed that by using the wildcard character "*" at the end of the
name "TestCases", this would count for it - either i am wrong with my
assumption or i implemeted incorrectly.

if any of you could point out what i am doing wrong or maybe there is
a more elegant way of writing above code, this would be mostly
appreciated.

cheers....


...jurgenC!

count Worksheet using Wildcard
 

hi Bob and Heiko,

thank you very much for your comments, both are very elegant.

cheers....


....jurgenC!

remove "somewhere" from eMail when replying direct

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

count Worksheet using Wildcard
 

Same as you have , but try "like" instead of = for your
If statement.

Merry Christmas

Sub countTestCases()
Dim Sht As Object
Dim nr As Integer
nr = 0
For Each Sht In ActiveWorkbook.Sheets
If Sht.name like "TestCases*" Then nr = nr + 1
Next
MsgBox nr
End Sub
-----Original Message-----
hi all,

find following my Sub:

Sub countTestCases()
Dim Sht As Object
Dim nr As Integer
nr = 0
For Each Sht In ActiveWorkbook.Sheets
If Sht.name = "TestCases*" Then nr = nr + 1
Next
MsgBox nr
End Sub

this is simply to count how many Worksheets starting
with the name
"TestCases" are in the current Workbook. for example
there might be
"TestCases0",
"TestCases5" and
"TestCases14",
if i execute above routine all i end up with is 0 for nr
(but my goal
is it to display 3).

i assumed that by using the wildcard character "*" at
the end of the
name "TestCases", this would count for it - either i am
wrong with my
assumption or i implemeted incorrectly.

if any of you could point out what i am doing wrong or
maybe there is
a more elegant way of writing above code, this would be
mostly
appreciated.

cheers....


....jurgenC!
.

count Worksheet using Wildcard
 

hi David,

merry xMas to you too - thanks for your solution, mostly appreciated.

cheers...

...jurgenC!

remove "somewhere" from eMail when replying direct

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!