Code help - I think this will be an easy one
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
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





RE: Code help - I think this will be an easy one
......
ELSE
......
END IF
RE: Code help - I think this will be an easy one
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
Seems to work perfect. :)
RE: Code help - I think this will be an easy one
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
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
Dik
RE: Code help - I think this will be an easy one
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
Dik
RE: Code help - I think this will be an easy one
RE: Code help - I think this will be an easy one
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 SelectSkip,
for a NUance!
RE: Code help - I think this will be an easy one
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
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
RE: Code help - I think this will be an easy one
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: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm