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

[davefish]

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-[highlight #FCE94F]4UN[/highlight]-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

 

[DjangMan]

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

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

 

[mintjulep]

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?

 

[Andrzejek]

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:
[pre]
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
[/pre]

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.

 

[davefish]

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-[highlight #FCE94F]8UN[/highlight]-IN-HH-024D-R to the code and if a mod level UC625-12-8UN-IN-HH-024D-R-[highlight #FCE94F]M3[/highlight] and finally option T gives UC625-12-8UN-IN-HH-024D-R-M3-[highlight #FCE94F]T[/highlight]. 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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[davefish]

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-[highlight #FCE94F]M4-T[/highlight]shows a fully populated unit with M & T options at the end, but UC625-12-[highlight #FCE94F]8UN[/highlight]-RD-HL-024D-[highlight #FCE94F]R-T[/highlight] 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

 

[mintjulep]

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?

 

[SkipVought]

1) paste my function into a module in your workbook.
2) in H10 enter this formula...
[tt]
=MakePN(D1120)
[/tt]
...assuming that your Part Number Character range is D1120.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

@Skip,

Yeah, like that

 

[SkipVought]

I just noticed an almost hidden gocha. The OP has an instance of
[tt]
H
H
[/tt]
...that ends up as...
[tt]
HH
[/tt]
?????

And whats with
[tt]
048D
[/tt]
...that becomes
[tt]
048
[/tt]
.???


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

Looks like there should be a "-" between the 2 Hs and no D after 48

https://www.mtl-inst.com/images/upl...rocess_alarm_equipment/EPS RTK UC625 rev4.pdf

Glad that I no longer own stock in this company.

 

[SkipVought]

What’s the LOGIC here?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[davefish]

Hi Skip,

The formula =MakePN(D1120) 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