×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# If and else blocks and embedded loops

## If and else blocks and embedded loops

(OP)
Hi guys,

I have a large spreadsheet of data that I am trying to extract data from and put into .txt files for a programme I have got for joint analysis.
My problem is that I have one cell that informs me whether or not the joint is on the shell or the channel of a heat exchanger.
Depending on that answer I want to check either the shell or channel column to see if it contains liquid or gas and then assign a value in a separate spreadsheet dependant on the result.

So essentially I have one overarching loop (red) and two sub loops (green and blue).

Check Cell x for shell or channel (as text)

If Cell x is shell Check Cell y for Oil, water or Gas (as text)

If Cell y is Oil or Water then Value = 1
Else If Cell y is Gas then Value =0
End If

Else If Cell x is channel Check Cell z for Oil, water or Gas (as text)

If Cell z is Oil or Water then Value = 1
Else If Cell z is Gas then Value =0
End If

End If

I cannot get this to work and have had a stab using Instr command to some success I just cant get the loop logic to work. If anyone has experience in embedding loops etc I would really appreciate any help. E.g. when I type an else statement how do I know which If statement it corresponds too?

Kind Regards,

Nick

### RE: If and else blocks and embedded loops

Please tell us what you need to do. Not how you think it should be done.

### RE: If and else blocks and embedded loops

#### Quote:

E.g. when I type an else statement how do I know which If statement it corresponds too?
That would be the most recent if that is not already "closed" with an else or an end if.

=====================================
(2B)+(2B)' ?

### RE: If and else blocks and embedded loops

The structure suggested by your color coding looks correct to me fwiw

=====================================
(2B)+(2B)' ?

### RE: If and else blocks and embedded loops

(OP)
In the excel file it should read check the value in column I not U (sorry typo)

### RE: If and else blocks and embedded loops

#### Quote:

with instructions of what I want to do using a VBA macro.

Please tell us what you need to do. Not how you think it should be done.

### RE: If and else blocks and embedded loops

If I was wrestling with this sort of a problem (assuming the code does not give an error, but rather "just" gives a wrong answer) I would set up a very simple spreadsheet with a very simple problem that demanded the same sort of logic, then test things out thoroughly.  Pepper the VBA code with MSGBOXes to give me an idea what is happening.  Etc.  It might reveal that the problem lies somewhere other than in the logic structure you present above

(Apologies if you have already done this.)

### RE: If and else blocks and embedded loops

Your code appears to be pseudocode, which makes it difficult to check, but:
if you want to run an If/ElseIf loop the first line should stop at the Then
assign the value of the cell to a variable before checking
use = to check if the two things are equal
do a separate check for "Oil" and "Water"
enclose the check text string in quotes

If y = "Oil" or y = "Water" Then
Value = 1
ElseIf y = "Gas" Then
Value = 0
End If

As suggested above, it looks like you need to start with something much simpler and get that working, before worrying about the nested ifs.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: If and else blocks and embedded loops

I agree with IDS, pseudocode is good for developing your ideas, but if the error lies in translating pseudocode to vba code, we can't see it here.

I also agree with Denial, it's great to develop your own troubleshooting skills because (if you're like me), you are going to make a lot more mistakes along the way and finding easy ways to track them down is invaluable. Myself, I took some time to (at least partially) familiarize myself with the considerable tools available in the Interactive Display Environment.

First one, you've gotta use is immediate window. Execute a single command from there any time you want, either to check effects of a command or to examine variables. That is useful not just during troubleshooting but also while writing your code to begin with (to check right away if a statement you're about to write will have desired effect).

Speaking of examining variables, there is the locals window for checking values of varibles.

Finally to pull it all together, I tend to put a "stop" statement just before the code I think may be malfunctioning. Then then when it stops I am taken to the vba window where I can single step through the code, making sure it takes the right paths and checking the variables have the values that I expect along the way.

=====================================
(2B)+(2B)' ?

### RE: If and else blocks and embedded loops

There are more sophisticated tools available also: breakpoints, variable watches etc.

=====================================
(2B)+(2B)' ?

### RE: If and else blocks and embedded loops

Another tip.  Taking IDS's code snippet a bit further, include an "Else" action.  Thus you can cover the "impossible" case where, say, y="Fish" with something like:

#### CODE

If y = "Oil" or y = "Water" Then
Value = 1
ElseIf y = "Gas" Then
Value = 0
Else
MSGBOX "The impossible has happened."
End If 
After all, if it can never happen what's the harm in including code to trap it?

### RE: If and else blocks and embedded loops

#### Quote (nick)

Check Cell x for shell or channel (as text)

If Cell x is shell Check Cell y for Oil, water or Gas (as text)

If Cell y is Oil or Water then Value = 1
Else If Cell y is Gas then Value =0
End If

Else If Cell x is channel Check Cell z for Oil, water or Gas (as text)

If Cell z is Oil or Water then Value = 1
Else If Cell z is Gas then Value =0
End If

End If
As stated by IDS, it can be substantially simplified to eliminate the nested structure.
I’ll spell it out in more details including the suggestion of Denial.
Also make the code case insensitive using "upper"
This assumes that X, Y, Z refer to cells where string data is stored

#### CODE

Dim YZchoice as string
If upper(X.value) = “SHELL” then
YZchoice = upper(Y.value)
elseif upper(X.value) = “CHANNEL”
YZchoice = upper(Z.value)
else
msgbox “Error.. Error…”
endif

If YZchoice = “OIL” or YZchoice = “WATER” then
Value = 1
Elseif YZchoice = “GAS”
Value = 0
Else
msgbox “Dr Smith… does not compute!”
Endif 

=====================================
(2B)+(2B)' ?

### RE: If and else blocks and embedded loops

(OP)
Hi everyone,

Thank you so much for your help, I managed to sort this by just using two blocks of code as opposed to one thanks to your tips. Essentially I just set one check to ascertain my first variable and assign a value. Then in a second seperate loop/piece of code checked the other value in a column dependant on the prevously assigned value. Much easier this way than trying to use multiple if statements all in the same loop. May not be the neatest way but it works!

Cheers guys really appreciate the help.

### RE: If and else blocks and embedded loops

(OP)
Ok I managed to get it working how I wanted in the first place. For those interested the Excel with code is attached or see below:

#### CODE -->

Sub test()

Dim i As Integer

Worksheets("Sheet1").Activate

For i = 3 To 6 Step 1

If InStr(1, Worksheets("Sheet1").Cells(i, 3).Value, "Channel", 0) <> 0 Then
If InStr(1, Worksheets("Sheet1").Cells(i, 2).Value, "Oil", 0) <> 0 Or InStr(1, Worksheets("Sheet1").Cells(i, 2).Value, "Water", 0) <> 0 Then
Cells(i, 4).Value = 10
ElseIf InStr(1, Worksheets("Sheet1").Cells(i, 2).Value, "Gas", 0) <> 0 Then
Cells(i, 4).Value = 20
End If
ElseIf InStr(1, Worksheets("Sheet1").Cells(i, 3).Value, "Shell", 0) <> 0 Then
If InStr(1, Worksheets("Sheet1").Cells(i, 1).Value, "Oil", 0) <> 0 Or InStr(1, Worksheets("Sheet1").Cells(i, 1).Value, "Water", 0) <> 0 Then
Cells(i, 4).Value = 10
ElseIf InStr(1, Worksheets("Sheet1").Cells(i, 1).Value, "Gas", 0) <> 0 Then
Cells(i, 4).Value = 20
End If
End If

Next i

End Sub 

Cheers all.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!