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
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
RE: If and else blocks and embedded loops
=====================================
(2B)+(2B)' ?
RE: If and else blocks and embedded loops
=====================================
(2B)+(2B)' ?
RE: If and else blocks and embedded loops
I hope this helps explain things.
Cheers Nick
RE: If and else blocks and embedded loops
RE: If and else blocks and embedded loops
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: If and else blocks and embedded loops
Please tell us what you need to do. Not how you think it should be done.
Many people, myself included, will not download files from random people.
RE: If and else blocks and embedded loops
(Apologies if you have already done this.)
RE: If and else blocks and embedded loops
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 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
=====================================
(2B)+(2B)' ?
RE: If and else blocks and embedded loops
CODE
RE: If and else blocks and embedded loops
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
=====================================
(2B)+(2B)' ?
RE: If and else blocks and embedded loops
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
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 SubCheers all.