R
Rick Rothstein
True, but Like is a VB programming concept and my guess is that most Excel
users are not VB literate, so I would think many (if not most) of the users
would not be familiar with Like's operator patterns. However, I think we can
cater to both types of users with something like this (off the top of the
head code) maybe...
Function Foo(A As Variant, ByVal P As String, _
Optional C As Boolean) As Double
Dim X As Variant
If Not P Like "*[*?#[]*" Then P = "*[" & P & "]*"
If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A)
For Each X In A
If C Then
If UCase(X) Like UCase(P) Then Foo = Foo + 1
ElseIf X Like P Then
Foo = Foo + 1
End If
Next X
End Function
If the user uses an asterisk, question mark, hash mark or left square
bracket in his/her pattern string, then the code assumes the user has
constructed a Like pattern string and passes it through as it; if none of
those characters are present, then the code assumes the user is looking for
a simple letter match. The optional C argument still optionally allows for
case sensitive or case insensitive searches.
users are not VB literate, so I would think many (if not most) of the users
would not be familiar with Like's operator patterns. However, I think we can
cater to both types of users with something like this (off the top of the
head code) maybe...
Function Foo(A As Variant, ByVal P As String, _
Optional C As Boolean) As Double
Dim X As Variant
If Not P Like "*[*?#[]*" Then P = "*[" & P & "]*"
If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A)
For Each X In A
If C Then
If UCase(X) Like UCase(P) Then Foo = Foo + 1
ElseIf X Like P Then
Foo = Foo + 1
End If
Next X
End Function
If the user uses an asterisk, question mark, hash mark or left square
bracket in his/her pattern string, then the code assumes the user has
constructed a Like pattern string and passes it through as it; if none of
those characters are present, then the code assumes the user is looking for
a simple letter match. The optional C argument still optionally allows for
case sensitive or case insensitive searches.