• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Looping if condition

Hi Experts,

I have the following VBA code and need to simplify.

If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_001" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_002" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_003" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_004" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_005" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_006" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_007" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_008" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_009" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_010" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_011" Then
                nodX.Expanded = True
            End If
            If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_012" Then
                nodX.Expanded = True
            End If
 

my Sheets("Compiling").Range("B1").Value is same "SS_001_CER_018_" except the last three digits (001) and it goes till 012 and I need put them in a loop or simplify this code as soon as possible.
Can you please help me out simplifying this code?

0
Tpaul_10
Asked:
Tpaul_10
4 Solutions
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Something like...

    Dim i As Integer
    For i = 1 To 12
        If Sheets("Compiling").Range("B1").Value = "SS_001_CER_018_" & Format(i, "000") Then
            nodX.Expanded = True
            Exit For
        End If
    Next i
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters....

Select Case Sheets("Compiling").Range("B1").Value
   Case  "SS_001_CER_018_001", "keep adding your criteria here", "and here", "and here too"
      nodX.Expanded = True
   Case Else
      nodX.Expanded = False
End Select
0
 
EDDYKTCommented:
Or


Dim i as integer

i = CInt(Right(Sheets("Compiling").Range("B1").Value , 3))
if (i > 0 and i < 13) then nodX.Expanded = True
0
 
aikimarkCommented:
1. I wish I could have used the VB Like operator, but it wouldn't work. :-(

2. You can use the regular expression class if you add the scripting runtime reference to your project.

3. Simplest solution is

Select Case Sheets("Compiling").Range("B1").Value
   Case  "SS_001_CER_018_001" To "SS_001_CER_018_012"
      nodX.Expanded = True
End Select
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now