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

[Kenja824]

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

 

[davidbeach]

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

 

[IDS]

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/

 

[Kenja824]

Thanks davidbeach

Seems to work perfect.

 

[Kenja824]

Sorry IDS, Didnt see your reply til now.

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?

 

[IDS]

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/

 

[dik]

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

Dik

 

[Kenja824]

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.

 

[dik]

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

 

[IFRs]

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.

 

[SkipVought]

I like using Select Case...End Select

    '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!}

 

[dik]

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

Dik

 

[SkipVought]

The Else [highlight #FCE94F]value[/highlight] for xorv and gorb is [highlight #FCE94F]“”[/highlight], the assigned initial value.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

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

 

[IRstuff]

https://www.techonthenet.com/excel/formulas/case.php

Case Else

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm