Solved

# Looping if condition

Posted on 2006-04-25
156 Views
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.

0
Question by:Tpaul_10

LVL 85

Assisted Solution

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

LVL 65

Assisted Solution

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

LVL 26

Accepted Solution

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

LVL 44

Assisted Solution

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

### Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…