Search wildcard to limit only alpha characters
 

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.

Search wildcard to limit only alpha characters
 

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


"excelnut1954" wrote:

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.

Search wildcard to limit only alpha characters
 

On Feb 22, 9:07 am, Tom Ogilvy
wrote:
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



"excelnut1954" wrote:
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.- Hide quoted text -

- Show quoted text -

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