Search wildcard to limit only alpha characters

E

excelnut1954

The user wants to find a record on a list. He enters a PO#, and it
searches the list.
Example. User enters in M123456.
The list contains this number, but also has M123456A, M123456B and
M123456C.
The userform comes up showing the 1st record, M123456.

The code below is in the useform, and it counts the number of records
with the PO#, plus variations at the end of the PO#. In the above
example, Texbox 25 will show the number 4. This works fine.

CountPO = Application.CountIf(Range("POCurrent_Column"), FindPOVal &
"*")
TextBox25.Value = CountPO

What I want is to narrow it down to cause the wildcard in the above
code (FindPOVal & "*") to only look for alpha characters at the end
of the value FindPOVal.

So, if M123456 and M123456A, M123456B and M123456C are on the list,
textbox 25 will correctly show the number 4. BUT, if for some reason
there is also M123456789 on the list, it won't be counted, and textbox
25 will still show the number 4.

Thanks,
J.O.
 
T

Tom Ogilvy

Sub AAA()
Dim countPO As Long, v As Variant
Dim i As Long, s As String
Dim FindPoval As String
FindPoval = "M123456"
v = Range("POCurrent_Column")
countPO = 0
For i = LBound(v) To UBound(v)
If InStr(1, v(i, 1), FindPoval, vbTextCompare) = 1 Then
If Len(v(i, 1)) = Len(FindPoval) Then
countPO = countPO + 1
Else
s = Right(v(i, 1), Len(v(i, 1)) - Len(FindPoval))
If Not IsNumeric(Left(s, 1)) Then
countPO = countPO + 1
End If
End If
End If
Next
TextBox25.Value = countPO

End Sub
 
E

excelnut1954

Sub AAA()
Dim countPO As Long, v As Variant
Dim i As Long, s As String
Dim FindPoval As String
FindPoval = "M123456"
v = Range("POCurrent_Column")
countPO = 0
For i = LBound(v) To UBound(v)
If InStr(1, v(i, 1), FindPoval, vbTextCompare) = 1 Then
If Len(v(i, 1)) = Len(FindPoval) Then
countPO = countPO + 1
Else
s = Right(v(i, 1), Len(v(i, 1)) - Len(FindPoval))
If Not IsNumeric(Left(s, 1)) Then
countPO = countPO + 1
End If
End If
End If
Next
TextBox25.Value = countPO

End Sub

--
Regards,
Tom Ogilvy










- Show quoted text -

Thanks, Tom. I'll give it a try later today. I appreaciate your help.
J.O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top