×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-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.

Students Click Here

Jobs

Dynamic part number creation...Should I use Select case, if statement or combination

Dynamic part number creation...Should I use Select case, if statement or combination

Dynamic part number creation...Should I use Select case, if statement or combination

(OP)
I have a part number string that needs to be dynamic enough to handle null values for certain components. This takes the form UC625-16-4UN-GN-HH-R-048-M3-T where if 4UN is a null value it's removed from the string. Similarly M3 & T can be removed in the same manner. What I'm struggling with the logic that encompasses all three. The PN structure also need to be maintained. The code I have tried is:-

Any help or guidance is appreciated

Sheets("Data Entry").Select 'Take all information from this sheet

Dim unit_Type As String
Dim Input_count As Integer
Dim Un_armed As String
Dim LED As String
Dim Pri_PSU As String
Dim Aux_PSU As String
Dim Repeat As String
Dim FCV As String
Dim M_Option As String
Dim Trop As String

unit_Type = [D11].Value
Input_count = [D12].Value
Un_armed = [D13].Value
LED = [D14].Value
Pri_PSU = [D15].Value
Aux_PSU = [D16].Value
Repeat = [D17].Value
FCV = [D18].Value
M_Option = [D19].Value
Trop = [D20].Value

Select Case Un_armed
Case Is = " ":
If M_Option = " " Then
[N3].Value = "One"
ElseIf M_Option = "M3" Then
[N3].Value = "Two"
ElseIf Trop = "T" Then
[N3].Value = "Three"

End If

[H10].Value = unit_Type & "-" & Input_count & "-" & LED & "-" & Pri_PSU & Aux_PSU
If M_Option = "M3" Then
[N3].Value = "Boo"
End If

Case Is <> " ": [H10].Value = unit_Type & "-" & Input_count & "-" & Un_armed & "-" & LED & "-" & Pri_PSU & Aux_PSU & "-" & Repeat & "-" _
& FCV & "-" & M_Option & "-" & Trop

RE: Dynamic part number creation...Should I use Select case, if statement or combination

What about concatenating everything and then replacing pairs of hyphens with a single hyphen?

e.g. your example formatted item
UC625-16-4UN-GN-HH-R-048-M3-T

could end up as
UC625-16--GN-HH-R-048--

CODE

Dim StartingItem As String
Dim FinalItem As String
StartingItem = "UC625-16--GN-HH-R-048--"
FinalItem = StartingItem
While InStr(1, FinalItem, "--") > 0
    FinalItem = Replace(FinalItem, "--", "-")
Wend
MsgBox FinalItem 'Will show UC625-16-GN-HH-R-048 

I used the while loop just in case your 'starting item' ends up with three or more dashes:
UC625-16---HH-R-048--

RE: Dynamic part number creation...Should I use Select case, if statement or combination

"I have a part number string " Where? Nothing in your example code looks like it might be this string?

"where if 4UN is a null value it's removed from the string" Duh? That's sort of what null means.

"What I'm struggling with theis logic that encompasses all three."

"The PN structure also need to be maintained." What structure?

RE: Dynamic part number creation...Should I use Select case, if statement or combination

Based on this:
unit_Type = [D11].Value
Input_count = [D12].Value
Un_armed = [D13].Value
LED = [D14].Value
Pri_PSU = [D15].Value
Aux_PSU = [D16].Value
Repeat = [D17].Value
FCV = [D18].Value
M_Option = [D19].Value
Trop = [D20].Value

it looks to me this in Excel we have:
     D
   .....
11   UC    --> unit_Type
12  625    --> Input_count
13   16    --> Un_armed
14  4UN    --> LED
15   GN    --> Pri_PSU
16   HH    --> Aux_PSU
17    R    --> Repeat
18  048    --> FCV
19   M3    --> M_Option
20    T    --> Trop
 

Just a guess here about it....

Who knows what's in Columns A-C and Rows 1 - 10...


---- Andy

There is a great need for a sarcasm font.

RE: Dynamic part number creation...Should I use Select case, if statement or combination

(OP)
Hi Guy's

Hopefully this will clarify some our your questions:-

For a system that has all attributes the part number code looks like this UC625-12-IN-HH-024D-R if There is an Un_armed value it adds UC625-12-8UN-IN-HH-024D-R to the code and if a mod level UC625-12-8UN-IN-HH-024D-R-M3 and finally option T gives UC625-12-8UN-IN-HH-024D-R-M3-T. It could be that the code has any of these present such as UC625-12-IN-HH-024D-R-T.

My use of the term Null may be misleading too., All I meant was that the element UN, M or T has a 0 or " " (Space) as its value.

A question DjangMan , the result is to be entered in an Excel cell. What's the best way to handle that?

Many thanks gents

RE: Dynamic part number creation...Should I use Select case, if statement or combination

Quote:

the result is to be entered in an Excel cell. What's the best way to handle that?

The devil is in the details.

Could be that a Function() would work. Dunno.

Could be that a Sub() with a button would be better. Dunno.

We need to know how your sheet works. How do you intend to functionally, to get source data on the sheet in column D and then the assembled Part Number somewhere else on this sheet or another sheet?

Tell us, step by step, how that will happen, not what happens in the black box that is the VBA code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Dynamic part number creation...Should I use Select case, if statement or combination

...and continuing my reply from above, it could be as simple as this, BUT I'm assuming a very limited set of other criteria...

CODE

Function MakePN(rng As Range)
'concatenated all values in rng separated by - (DASH CHARACTER)    
    Dim r As Range, sVal As String
    
    For Each r In rng
        sVal = r.Value

        If sVal <> "" Then
            MakePN = MakePN & sVal & "-"
        End If
    Next
    
    MakePN = Left(MakePN, Len(MakePN) - 1)
End Function 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Dynamic part number creation...Should I use Select case, if statement or combination

(OP)
Hi Skip,

I'll try and explain what I need as follows:-

I have a number of Embedded Combo boxes on the sheet that allow the user to select certain values that make up a part number string. Some elements are optional and their position in the string is defined, so that. UC625-12-RD-HL-024D-R-M4-Tshows a fully populated unit with M & T options at the end, but UC625-12-8UN-RD-HL-024D-R-T shows some un_armed (8UN) element and that has an addition code (8UN, meaning 8UN, has to be added and the M option removed. If the M option is dropped this too is changed. I've tried this using formulae, but end up with double dashes. Any combination can exist with the three options and I struggle to find the right logic.

So... I dump each individual value into spread sheet cells ( see image 1) and I wish the whole string to be entered into cell H10 all on the same sheet as per Image 2 .

Where a value in the sheet is 0 the corresponding cell has a " " value. please see image3

I hope this makes sense?

Davefish

RE: Dynamic part number creation...Should I use Select case, if statement or combination

So,

You have some number of strings that you need to concatenate.

With a "-"between each.

Except if a string has no content then no dash is needed.

Yes?

RE: Dynamic part number creation...Should I use Select case, if statement or combination

1) paste my function into a module in your workbook.
2) in H10 enter this formula...

=MakePN(D11:D20)

...assuming that your Part Number Character range is D11:D20.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Dynamic part number creation...Should I use Select case, if statement or combination

@Skip,

Yeah, like thatpacman

RE: Dynamic part number creation...Should I use Select case, if statement or combination

I just noticed an almost hidden gocha. The OP has an instance of

H
H

...that ends up as...

HH

?????

And whats with

048D

...that becomes

048

.???


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Dynamic part number creation...Should I use Select case, if statement or combination

What’s the LOGIC here?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Dynamic part number creation...Should I use Select case, if statement or combination

...and there’s another issue: your Select Case Un_armed for assigning a value to N3, which does not seem to be at all related to the topic of this thread.

????

I’m having trouble tracking with you.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Dynamic part number creation...Should I use Select case, if statement or combination

(OP)
Hi Skip,

The formula =MakePN(D11:D20) does work.

With respect to the HH scenario, I changed how I presented this to the calculated this to cell D15 & D16 by concatenating them. and the whole process works just fine.

Thanks you very much for your focus and apologies for my not too clear explanations!

Regards

Dav

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 Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close