Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count two conditions using a wildcard? | Excel Worksheet Functions | |||
COUNT or COUNTIF using wildcard text? | Excel Worksheet Functions | |||
How do I count wildcard text meeting certain criteria in EXCEL? | Excel Worksheet Functions | |||
Wildcard Usage: I absolutely need this to count?/sum? two or more criteri | Excel Discussion (Misc queries) | |||
VBA: Select Excel's Worksheet using wildcard | Excel Programming |