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

# Code help - I think this will be an easy one3

## Code help - I think this will be an easy one

(OP)
I have code that was written for me once that I just learned has a flaw for getting the correct outcome. This function below is to read a long material spec in a cell and let me know if it is a G (Galvanized), B (uncoated or Bare), XG, XB, VG or VB which are all Galvanized or Uncoated high strength.

The problem is this code says if the spec has "uncoated" in the spec it is "B" (which is correct) If it has "-U*" It is "G". Which is not correct because the Uncoated materials also often have the -U in it.

What I need to do is change it so that if it has "Uncoated" in the spec, it is a "B" and if it does not have "Uncoated" in the spec, it is a "G".

I know excel IF statements in formulas are written so the last segment is the result if it is NOT what the others are. Can we do something like that in VB code?
How do I fix this code?

Public Function Material_code(x_order_string As String) As String
'This function searches through an order string for certain values, then returns a code based on those values

'dimension my variables as strings/text
Dim xorv As String
Dim gorb As String
Dim final_output As String

'give initial values of nothing - not v or b, not g or b to each variable
xorv = ""
gorb = ""
final_output = ""

'check the order string to see if it should be a x or v value, then g or b and set variables as appropriate
If x_order_string Like "*270LA*" Or x_order_string Like "*CR270*" Or x_order_string Like "*CR300*" Or x_order_string Like "*300LA*" Or x_order_string Like "*340_LA*" Or x_order_string Like "*340LA*" Or x_order_string Like "*380LA*" Or x_order_string Like "*420LA*" Or x_order_string Like "*500LA*" Or x_order_string Like "*550LA*" Then
xorv = "X"
ElseIf x_order_string Like "*T/*" Then
xorv = "V"
End If

If x_order_string Like "*UNCOATED*" Then
gorb = "B"
End If

If x_order_string Like "*-U*" Then
gorb = "G"

ElseIf x_order_string Like "*-E*" Then
gorb = "G"

End If

'combine two variables for final output code, then check for single digit values
final_output = xorv & gorb

If final_output = "X" Then
final_output = "XG"
End If

If final_output = "V" Then
final_output = "VG"
End If

Material_code = final_output

End Function

IF ..... THEN
......
ELSE
......
END IF

### RE: Code help - I think this will be an easy one

You could do:

If x_order_string Like "*UNCOATED*" Then
gorb = "B"
Else
gorb = "G"
End If

and delete:
If x_order_string Like "*-U*" Then
gorb = "G"
ElseIf x_order_string Like "*-E*" Then
gorb = "G"
End If

Or alternatively:
gorb = "G"
If x_order_string Like "*UNCOATED*" Then gorb = "B"

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

### RE: Code help - I think this will be an easy one

(OP)
Thanks davidbeach

Seems to work perfect. :)

### RE: Code help - I think this will be an easy one

(OP)

I have run into a problem with the other way.. If a cell is empty it still gives a G and I cant have that.

How do I make it so if a cell is empty, it doesnt return anything?

### RE: Code help - I think this will be an easy one

At the top of the code add:

If x_order_string = "" Then
Material_code = "" ' or return an error message
Exit function
End If

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

### RE: Code help - I think this will be an easy one

If you're using visual basic, can you use the CASE selection? When you have multiple selections it's often the most clear.

Dik

### RE: Code help - I think this will be an easy one

(OP)
Thanks IDS. That did it.

DIK, To be completely honest, Im not even sure what you mean. I find myself having to work with code a lot between Excel and NX Journals these days, but that doesnt seem to stop me from sucking at it. lol I can sometimes manipulate it some but thats my limit.

### RE: Code help - I think this will be an easy one

When I have 3 or more items to select from, rather than using nested 'if then else' statements, I use select case. The code is much neater and easier to read... More than three, it gets much easier. Good habit to get into... The statement is available in C, basic (almost all variants), pascal, and likely others...

Dik

### RE: Code help - I think this will be an easy one

Select - Case - Else is intended for exactly this situation and is much cleaner and easier to debug. I heartily encourage its use. If-Then-Else is intended for one or at most two selections and often results in very difficult to create, read and interpret code.

### RE: Code help - I think this will be an easy one

I like using Select Case...End Select

#### CODE

'check the order string to see if it should be a x or v value, then g or b and set variables as appropriate

Select Case True
Case x_order_string Like "*270LA*", x_order_string Like "*CR270*", x_order_string Like "*CR300*", x_order_string Like "*300LA*", x_order_string Like "*340_LA*", x_order_string Like "*340LA*", x_order_string Like "*380LA*", x_order_string Like "*420LA*", x_order_string Like "*500LA*", x_order_string Like "*550LA*"
xorv = "X"
Case x_order_string Like "*T/*"
xorv = "V"
End Select

Select Case True
Case x_order_string Like "*UNCOATED*"
gorb = "B"
Case x_order_string Like "*-E*"
gorb = "G"
End Select

'combine two variables for final output code, then check for single digit values
final_output = xorv & gorb

Select Case final_output
Case "x"
final_output = "XG"
Case "V"
final_output = "VG"
End Select 

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Code help - I think this will be an easy one

Thanks SKip... vindicated... thought I was being too anal... I usueally throw in a 'else' to trap anything that shouldn't be there...

Dik

### RE: Code help - I think this will be an easy one

The Else value for xorv and gorb is “”, the assigned initial value.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Code help - I think this will be an easy one

(OP)
Thanks Everyone

I can see where it looks cleaner, and I changed my code to use Select Case. Though it does still seem I needed to use the If Else statement for the B or G output.
The possible specs can have several different ends to it. -U, -E, -Z, -T etc... and even the Uncoated specs will still have the -U after it. But everything that is not Uncoated should be a "G". The original code was messing up with this.

Wow.... As simple as it is for you guys, the fact that I am actually understanding this enough to make that change is a huge breakthrough for me. lol ..... Now you are going to burst my bubble and tell me I shouldnt use the "If Else" statement right? lol

Really though, I do appreciate the help.
Thanks

#### 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!