suggestions for parsing a string

[dan dungan]

Hi programmers,

I've been tasked to build an application that will return a price when
the user enters a part number.

So, using Excel 2000 , I'm attempting to parse part numbers.

See the details below for more explanation.

Thanks,

Dan

I type a part number in cell A1:

Sub Test()

Dim Parts As String
'BAN stands for Basic Adapter Number
Dim BAN As Variant
Dim tBAN As String
Dim strBAN As String
Dim strVar1 As String
Dim strVar2 As String
Dim strVar3 As String

strVar1 = "217"
strVar2 = "265"
strVar3 = "266"
strBAN = strVar1 & "," & strVar2 & "," & strVar3
BAN = Split(strBAN, ",")
Parts = Range("A1").Value

tBAN = Mid(Parts, 1, 3)
If BAN = tBAN Then < run time error 13-type mismatch >
Range("B2").Value = BAN
End If

End Sub

____________________________________________________


Details:
Here are samples:

1 - 26515A06041056CZ
2 - 26615A06041056CZ
3 - 21715A06041056CZ
4 - E4101S08041N0551
5 - 901-704100356S

In the examples 1-3, I pull the core part price based on the basic
adapter number and connector code with a vlookup formula.

Basic Adapter Number
1 - 265
2 - 266
3 - 217

Connector Code
1-15
2-15
3-15

4. E4101S08041N0551

Example 4 doesn't have a basic adapter number. It has a
Function Designator = E
Connector Code=41
Series part number=01

5. 901-704100356S

Example 5 has doesn't have Basic Adapter number, Function Designator
or Series part number. It has a Basic Part Number

Basic Part Number = 901-704

 

[Barb Reinhardt]

Here are some modifications to your code. I don't know how you determine
your prices, so couldn't go any further.

Option Explicit
Sub Test()

Dim Parts As String
'BAN stands for Basic Adapter Number
Dim BAN As Variant
Dim BasicPartNo As String
Dim FuncDesignator As String
Dim ConnectorCode As String
Dim SeriesPartNo As String

Dim myRange As Range
Dim r As Range
Dim lRow As Long

Dim aWS As Worksheet
Set aWS = ActiveSheet

Set myRange = aWS.Range("A2") '<~~~First cell of range
lRow = aWS.Cells(aWS.Rows.Count, myRange.Column).End(xlUp).Row
Set myRange = myRange.Resize(lRow - myRange.Row + 1, 1)

For Each r In myRange
Debug.Print r.Address, r.Text,
If Mid(r.Text, 4, 1) = "-" Then
BasicPartNo = Left(r.Text, 7)
Debug.Print "Basic Part Number: "; BasicPartNo
ElseIf Left(r.Text, 1) = "E" Then 'If it's other than E, this won't work.
FuncDesignator = Left(r.Text, 1)
ConnectorCode = Mid(r.Text, 2, 2)
SeriesPartNo = Mid(r.Text, 4, 2)
Debug.Print "FuncDesignator: "; FuncDesignator, _
"Connector Code: "; ConnectorCode, _
"SeriesPartNo: "; SeriesPartNo
Else
BAN = Left(r.Text, 3)
ConnectorCode = Mid(r.Text, 4, 2)
Debug.Print "BAN: "; BAN, "Connector Code: ", ConnectorCode
End If
'Basic Part Number
Next r

End Sub

 

[dan dungan]

Thanks Barb,

I'll look into this and get back with my results.

Dan

 

[Rick Rothstein \(MVP - VB\)]

I see Barb has given you some direction to consider, so I'll let you
continue with her on that... I just wanted to point out to you why you got
the Type Mismatch error you showed us. You Dim'med BAN as a variant and then
assigned the output from a Split function to it. Since the output from a
Split function is a String array, BAN contains a String array. tBan is
Dim'med as String and you assign the output from the Mid function to it, so
it properly contains a String value. Now, you attempt to execute this
statement....

If BAN = tBAN Then

The reason for the mismatch error is because the BAN contains an array and
you are asking if that array equal a simple String... arrays (whether String
or otherwise) are different data types from simple Strings so you can't ask
if they are equal. You probably want to check one of the element of BAN
against tBAN, maybe this...

If BAN(0) = tBAN Then

I also wanted to point out that this statement...

tBAN = Mid(Parts, 1, 3)

being that it starts at the first character, can be written this way
instead...

tBAN = Left(Parts, 3)

Rick

 

[dan dungan]

Thanks Rick,

I'll look into this as well.

Dan

I see Barb has given you some direction to consider, so I'll let you
continue with her on that... I just wanted to point out to you why you got
the Type Mismatch error you showed us. You Dim'med BAN as a variant and then
assigned the output from a Split function to it. Since the output from a
Split function is a String array, BAN contains a String array. tBan is
Dim'med as String and you assign the output from the Mid function to it, so
it properly contains a String value. Now, you attempt to execute this
statement....

If BAN = tBAN Then

The reason for the mismatch error is because the BAN contains an array and
you are asking if that array equal a simple String... arrays (whether String
or otherwise) are different data types from simple Strings so you can't ask
if they are equal. You probably want to check one of the element of BAN
against tBAN, maybe this...

If BAN(0) = tBAN Then

I also wanted to point out that this statement...

tBAN = Mid(Parts, 1, 3)

being that it starts at the first character, can be written this way
instead...

tBAN = Left(Parts, 3)

Rick

 

[dan dungan]

Hi Barb,

You wrote:
<Here are some modifications to your code. I don't know how you
determine
your prices, so couldn't go any further.>

Is there a way to send a pdf file so you could see how I'm determining
the prices.
It is complicated for me to explain.

Thanks,

Dan