Hi,

I'm trying to parse a part number for quoting.

The part numbers could be, for example:

21718R22C1210055
21718R100440055
E1801B10031B0403
A1801A10069N0055
A3216S63100N1421

In the following code snippet,
I want to return into a string variable, EntrySize,
for a 217 type part, a certain part of a string Mid(r.Text, 10, 2),
if Mid(r.text,9,1)= chr(67), or chr(65) or
chr(68) or chr(45) or chr(82).

If not, I want EntrySize to equal Mid(r.text,9,2)

Below, I was trying to make a sAlph hold that variable, but I don't
know how to write it.

I'm grateful for any assistance.

Thanks,

Dan
----------------------------------------------------------------------------------------

ElseIf Left(r.Text, 1) = "E" Or Left(r.Text, 1) = "A" Then_
'If it's other than E or A, this won't work.
FuncDesignator = Left(r.Text, 1)
ConnectorCode = Mid(r.Text, 2, 2)
SeriesPartNo = Mid(r.Text, 4, 2)
Style = Mid(r.Text, 6, 1)
RayFormula = FuncDesignator & "XX" &_
SeriesPartNo & Style
OrderNum = Mid(r.Text, 7, 2)
EntrySize = Mid(r.Text, 9, 2)
Clamp = Mid(r.Text, 11, 1)
With Worksheets("A&E")
Set myDestRng = .Range("D2")
.Range("D2") = RayFormula
Set myDestRng = .Range("E2")
.Range("E2") = ConnectorCode
Set myDestRng = .Range("F2")
.Range("F2") = OrderNum
Set myDestRng = .Range("H2")
.Range("H2") = EntrySize
Set myDestRng = .Range("I2")
.Range("I2") = Clamp
End With
End

Else
BAN = Left(r.Text, 3)
'Debug.Print "BAN: "; BAN, "Connector Code:_
", ConnectorCode
ConnectorCode = Mid(r.Text, 4, 2)
sAlph = Mid(r.Text, 9, 1)

Style = Mid(r.Text, 6, 1)
RayFormula = BAN & "XX" & Style
OrderNum = Mid(r.Text, 7, 2)

?EntrySize = If sAlph=true, then
EntrySize = Mid(r.Text, 9, 2)

Clamp = Mid(r.Text, 11, 1)
With Worksheets("217")
Set myDestRng = .Range("E2")
.Range("E2") = RayFormula
Set myDestRng = .Range("G2")
.Range("G2") = ConnectorCode
Set myDestRng = .Range("K2")
.Range("K2") = OrderNum
Set myDestRng = .Range("H2")
.Range("H2") = EntrySize
Set myDestRng = .Range("I2")
.Range("I2") = Clamp
End With
End
End If

dan dungan wrote:

Hi Dan, not sure if I totally get the question, but see within. Also,
why so many chr() instead of plain text? chr(67) chr(65) chr(68) chr(45)
chr(82) is just strings C A D - R

Hi,

I'm trying to parse a part number for quoting.

The part numbers could be, for example:

21718R22C1210055
21718R100440055
E1801B10031B0403
A1801A10069N0055
A3216S63100N1421

In the following code snippet,
I want to return into a string variable, EntrySize,
for a 217 type part, a certain part of a string Mid(r.Text, 10, 2),
if Mid(r.text,9,1)= chr(67), or chr(65) or
chr(68) or chr(45) or chr(82).

If not, I want EntrySize to equal Mid(r.text,9,2)

Below, I was trying to make a sAlph hold that variable, but I don't
know how to write it.

I'm grateful for any assistance.

Thanks,

Dan
----------------------------------------------------------------------------------------

ElseIf Left(r.Text, 1) = "E" Or Left(r.Text, 1) = "A" Then_
'If it's other than E or A, this won't work.
FuncDesignator = Left(r.Text, 1)
ConnectorCode = Mid(r.Text, 2, 2)
SeriesPartNo = Mid(r.Text, 4, 2)
Style = Mid(r.Text, 6, 1)
RayFormula = FuncDesignator & "XX" &_
SeriesPartNo & Style
OrderNum = Mid(r.Text, 7, 2)
EntrySize = Mid(r.Text, 9, 2)
Clamp = Mid(r.Text, 11, 1)
With Worksheets("A&E")
Set myDestRng = .Range("D2")
.Range("D2") = RayFormula
Set myDestRng = .Range("E2")
.Range("E2") = ConnectorCode
Set myDestRng = .Range("F2")
.Range("F2") = OrderNum
Set myDestRng = .Range("H2")
.Range("H2") = EntrySize
Set myDestRng = .Range("I2")
.Range("I2") = Clamp
End With
End

Else
BAN = Left(r.Text, 3)
'Debug.Print "BAN: "; BAN, "Connector Code:_
", ConnectorCode
ConnectorCode = Mid(r.Text, 4, 2)
sAlph = Mid(r.Text, 9, 1)

Style = Mid(r.Text, 6, 1)
RayFormula = BAN & "XX" & Style
OrderNum = Mid(r.Text, 7, 2)

?EntrySize = If sAlph=true, then

' one way
If InStr(1, Chr(67) & Chr(65) & Chr(68) & Chr(45) & Chr(82), sAlph) Then

'easier to read
If InStr(1, "CAD-R", sAlph) Then

EntrySize = Mid(r.Text, 9, 2)

Clamp = Mid(r.Text, 11, 1)
With Worksheets("217")
Set myDestRng = .Range("E2")
.Range("E2") = RayFormula
Set myDestRng = .Range("G2")
.Range("G2") = ConnectorCode
Set myDestRng = .Range("K2")
.Range("K2") = OrderNum
Set myDestRng = .Range("H2")
.Range("H2") = EntrySize
Set myDestRng = .Range("I2")
.Range("I2") = Clamp
End With
End
End If

Hi Smartin

not sure if I totally get the question

I think you got the question. I had forgotten about instr.
And it seems to be working.

Also, why so many chr() instead of plain text?
chr(67) chr(65) chr(68) chr(45) chr(82) is just strings C A D - R

I'm converting this from formulas, and that's the way the formulas
were set up. I changed it as you showed in your example.

Thanks,

Dan