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?
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,351
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Why not just use If...ElseIf...End If? I can't see the point of Select Case here.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
@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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,351
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Legibility? ;) I have seen it said that If..End If is faster than Select Case, but have never bothered to test.
 

Forestq

Active Member
Joined
May 9, 2010
Messages
482
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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.
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top