Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search list for non-alpha characters | Excel Worksheet Functions | |||
WILDCARD CHARACTERS | Excel Programming | |||
Writing A Search Macro With Wildcard Characters? | Excel Programming | |||
wildcard characters | Excel Discussion (Misc queries) | |||
Wildcard for ALPHA only | Excel Discussion (Misc queries) |