Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String in a range
What is the Syntax to check if a string is in a range?
Thanks Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the POSITION IN A RANGE of text in a string that matches value(s) in a range | Excel Programming | |||
Retrieving range string from named range | Excel Programming | |||
Translate range name passed as string to a custom function to range addresses! | Excel Programming | |||
Passing a String in Array to Range as String | Excel Programming | |||
String to Range? | Excel Programming |