String in a range

[Guest]

What is the Syntax to check if a string is in a range?
Thanks Dan

 

[Norman Jones]

Hi Dan,

Try something like:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim Rng As Range
Dim Rng2 As Range
Const sStr As String = "ABC"

Set SH = ActiveSheet

With SH
Set Rng = .Range("A1")
Set Rng2 = .Range("A1:A10")
End With

MsgBox InStr(1, Rng.Value, sStr, vbTextCompare) > 0
MsgBox Application.CountIf(Rng2, sStr) > 0

End Sub
'<<=============

 

[Guest]

Thanks

Hi Dan,

Try something like:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim Rng As Range
Dim Rng2 As Range
Const sStr As String = "ABC"

Set SH = ActiveSheet

With SH
Set Rng = .Range("A1")
Set Rng2 = .Range("A1:A10")
End With

MsgBox InStr(1, Rng.Value, sStr, vbTextCompare) > 0
MsgBox Application.CountIf(Rng2, sStr) > 0

End Sub
'<<=============

 

[Guest]

Perhaps something like:

Function well_is_it(sr As Range, r As Range) As Boolean
well_is_it = False
s = sr.Value
For Each rr In r
If InStr(1, rr.Value, s) > 0 Then
well_is_it = True
Exit Function
End If
Next
End Function

 

[Norman Jones]

Hi Dan.

Should have been:

Another possibility might be::

'=============>>
Public Sub Tester2()
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim blFound As Boolean
Const sStr As String = "ABC"

Set SH = ActiveSheet
Set Rng = SH.Range("A1:A10")

For Each rCell In Rng.Cells
If InStr(1, rCell.Value, sStr, vbTextCompare) > 0 Then
blFound = True
Exit For
End If
Next rCell

MsgBox Prompt:="The string " & Chr(34) & sStr _
& Chr(34) & " was found = " & blFound
End Sub
'<<=============

You could also use the Find method.