Forestq

Active Member
Joined
May 9, 2010
Messages
482
hi,

I have macro where I'm opening workbooks and for diffrent sheets I want to run code.

Code:
...
Set wb = Workbooks.Open(strPath2 & strFile2)

Dim myArray() As String
Dim myCount, NumShts As Integer
NumShts = wb.Worksheets.Count
            
ReDim myArray(1 To NumShts)
            
For myCount = 1 To NumShts
    myArray(myCount) = wb.Sheets(myCount).Name
Next myCount

For Each ws In wb.Sheets
                       
  Select Case ws.Name
   Case Like "*Software*"
     'my code
   Case Like "*Hardware*"
     'my code
   Case Else
     '
   End Select
Next ws

But I can't use Like. What should I do to use CASE function?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Off the top and a bit of guessing...

You may believe that 'Dim myCount, NumShts as Integer' types both variables as Integer; it does not. You need 'myCount as Integer', or it is a Variant.

Both should be Longs though, as there is rarely the occasion where an Integer is required, and from 32-Bit on, Longs are faster.

For the Select Case, try (sorry, not tested) like:

Code:
Select Case True
Case ws.Name Like "*Software"
  'further code...
End Select

Does that help?

Mark
 
Upvote 0
Why not just use If...ElseIf...End If? I can't see the point of Select Case here.
 
Upvote 0
@Rory:

Do you know of an advantage of If...ElseIf...End If over a Select Case True? Just curiosity on the blond guy's part.

Mark
 
Upvote 0
Legibility? ;) I have seen it said that If..End If is faster than Select Case, but have never bothered to test.
 
Upvote 0
Hi GTO,

thanks, I tested on 2 files and works.

On the begining I want to use Elseif, but I thought that CASE will be better solutions.
 
Upvote 0
Legibility? ;)
Ahh, one's eyes' preferences. For my overly-tired ones, if it's more than a line or two, Case vs. ElseIf doesn't matter. If it's just a line at each Case, I "grab"

Case This = That: Something = Something Else

... quicker if that makes sense. It's one of the only times I can think of where using : seems to be easier to me.

I have seen it said that If..End If is faster than Select Case, but have never bothered to test.

I have read some folks objections to Select Case TRUE (hence the question) but have never seen a downside.

Anyways, nice to say "Hi" and hope all is terrific with you and yours. Thanks for the response :)

Mark
 
Upvote 0
Hi GTO,

thanks, I tested on 2 files and works.

On the begining I want to use Elseif, but I thought that CASE will be better solutions.

Glad we were of help and that it is working :)

Not sure if there's an advantage of one or the other.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top