String in a range
What is the Syntax to check if a string is in a range?
Thanks Dan |
String in a range
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 '<<============= --- Regards, Norman "Dan" wrote in message ... What is the Syntax to check if a string is in a range? Thanks Dan |
String in a range
Thanks
"Norman Jones" wrote: 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 '<<============= --- Regards, Norman "Dan" wrote in message ... What is the Syntax to check if a string is in a range? Thanks Dan |
String in a range
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 -- Gary''s Student - gsnu200733 "Dan" wrote: What is the Syntax to check if a string is in a range? Thanks Dan |
String in a range
Hi Dan. MsgBox Application.CountIf(Rng2, sStr) 0 Should have been: MsgBox Application.CountIf(Rng2.Value, sStr) 0 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. --- Regards, Norman |
All times are GMT +1. The time now is 02:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com