Case with like

[Forestq]

hi,

I have macro where I'm opening workbooks and for diffrent sheets I want to run 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?

 

[GTO]

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:

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

Does that help?

Mark

 

[RoryA]

Why not just use If...ElseIf...End If? I can't see the point of Select Case here.

 

[GTO]

@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

 

[RoryA]

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

 

[Forestq]

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.

 

[GTO]

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

 

[GTO]

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.