modules tweaking

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Haggr - 27 Jan 2007 21:56 GMT

the following module produces these results:

(xlb114r returns as xlb1114)
(xs245aaw returns as xs245w) the "W" in this case is important.

but I also need (xan72aa-7.5) to return as (xan72-7.5)

Public Function fGetFirstChars_Nums_w(pString As Variant) As String
On Error GoTo Err_fGetFirstCharsNums
Dim i As Integer
Dim boolNum As Boolean
Dim ch As String
Dim tmp As String

If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
ch = MID(pString, i, 1)
Select Case ch
Case "0" To "9"
If boolNum = False Then boolNum = True
tmp = tmp & ch
Case "w"
tmp = tmp & ch
If boolNum = True Then Exit For
Case "-", "/"
'ignore
Case Else
If boolNum = False Then
'no number char yet
tmp = tmp & ch
Else
Exit For
End If
End Select

Next
Else
tmp = vbNullString
End If

fGetFirstChars_Nums_w = tmp

Exit_fGetFirstCharsNums:
Exit Function

Err_fGetFirstCharsNums:
MsgBox Err.Description
Resume Exit_fGetFirstCharsNums
End Function

Reply to this Message

SteveS - 29 Jan 2007 06:38 GMT

Not sure what your question is..... maybe it is "Given an input of
'xan72aa-7.5', how do I modify my code to have the UDF return 'xan72-7.5'?"

3 examples is not a very big sample, so I won't guarantee the following code
snippets will work in every case.

Here are 3 ways to modify the Select Case() part of the code:

'x1---------------
Select Case ch
Case "0" To "9"
boolNum = True
tmp = tmp & ch
Case "w"
tmp = tmp & ch
If boolNum = True Then
Exit For
End If
Case "-", ".", "/"
tmp = tmp & ch
Case Else
If boolNum = False Then
'no number char yet
tmp = tmp & ch
End If
End Select

'--------------------------------

OR

'x2---------------
Select Case ch
Case "0" To "9", "-", "."
boolNum = True
tmp = tmp & ch
Case "w"
tmp = tmp & ch
If boolNum = True Then
Exit For
End If
Case Else
If boolNum = False Then
'no number char yet
tmp = tmp & ch
End If
End Select
'--------------

OR

'x3---------------
Select Case ch
Case "0" To "9"
boolNum = True
tmp = tmp & ch
Case "w"
tmp = tmp & ch
If boolNum = True Then
Exit For
End If
Case "-", ".", "/"
tmp = tmp & Right(pString, Len(pString) - i + 1)
Exit For
Case Else
If boolNum = False Then
'no number char yet
tmp = tmp & ch
End If
End Select
'---------------------

Replace your Select Case code with ONE of the above examples.

HTH

Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> the following module produces these results:
>
[quoted text clipped - 45 lines]
> Resume Exit_fGetFirstCharsNums
> End Function

Reply to this Message