If and else blocks and embedded loops

[nick262b]

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

 

[MintJulep]

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

 

[electricpete]

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)' ?

 

[electricpete]

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

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

 

[nick262b]

Hi MintJulep thanks for your quick response, sorry It isnt very clear I have attached an example xls with instructions of what I want to do using a VBA macro.

I hope this helps explain things.

Cheers Nick

 

[nick262b]

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

 

[IRstuff]

Was there supposed to be code in the file?

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[MintJulep]

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.

Many people, myself included, will not download files from random people.

 

[Denial]

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.)

 

[IDS]

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/

 

[electricpete]

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)' ?

 

[electricpete]

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

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

 

[Denial]

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:

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?

 

[electricpete]

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

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)' ?

 

[nick262b]

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.

 

[nick262b]

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:

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.