count Worksheet using Wildcard

  • Thread starter Thread starter jC!
  • Start date Start date
J

jC!

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!
 
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)
 
hi,
instead of If Sht.name = "TestCases*" Then nr = nr + 1
use If Left$(Sht.name,9) = "TestCases*" Then nr = nr + 1

hth
Heiko
 
hi Bob and Heiko,

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

cheers....


....jurgenC!

remove "somewhere" from eMail when replying direct
 
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
 
hi David,

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

cheers...

...jurgenC!

remove "somewhere" from eMail when replying direct
 
Back
Top