I have a string in VBA (variable "mystring") and text strings in Sheet1,
column A (A2:A100). I need to identify (in VBA) whether "mystring" is
already in the range of A2:A100, but I will be repeating this operation
thousands of times, so I'm looking for something better than just looping
A2:A100 and checking against each value individually, something more like:

If mystring (is in) Sheet1.range("A2:A100") then....

but I can't find the right keyword- I've looked for "contains", "includes",
"in", and others.... what is the proper way to write this?

Many thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Dim rng as Range
set rng = Range("A2:A100").Find(mystring, _
Lookin:=xlValues, Lookat:=xlpart)
if not rng is nothing then
msgbox "found in " & rng.Address
End if

--
Regards,
Tom Ogilvy


"KR" wrote in message
...
I have a string in VBA (variable "mystring") and text strings in Sheet1,
column A (A2:A100). I need to identify (in VBA) whether "mystring" is
already in the range of A2:A100, but I will be repeating this operation
thousands of times, so I'm looking for something better than just looping
A2:A100 and checking against each value individually, something more like:

If mystring (is in) Sheet1.range("A2:A100") then....

but I can't find the right keyword- I've looked for "contains",
"includes",
"in", and others.... what is the proper way to write this?

Many thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Keith,

If you could have multiples of the same value:

Sub FindValues2()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myString As String
Dim firstAddress As String

'This can also be set other ways

myString = InputBox("Enter the key word for search", , "Hello")

With Range("A2:A100")

Set c = .Find(myString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

'Then do what you want with all the cells that have been found, like:

MsgBox myString & " was found in " & d.Address(False, False)

End Sub

HTH,
Bernie
MS Excel MVP

"KR" wrote in message
...
I have a string in VBA (variable "mystring") and text strings in Sheet1,
column A (A2:A100). I need to identify (in VBA) whether "mystring" is
already in the range of A2:A100, but I will be repeating this operation
thousands of times, so I'm looking for something better than just looping
A2:A100 and checking against each value individually, something more like:

If mystring (is in) Sheet1.range("A2:A100") then....

but I can't find the right keyword- I've looked for "contains",
"includes",
"in", and others.... what is the proper way to write this?

Many thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Since he said:

right keyword- I've looked for "contains", "includes",
"in", and others....

you might want LookAt:=xlPart rather than LookAt:=xlWhole

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

If you could have multiples of the same value:

Sub FindValues2()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myString As String
Dim firstAddress As String

'This can also be set other ways

myString = InputBox("Enter the key word for search", , "Hello")

With Range("A2:A100")

Set c = .Find(myString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

'Then do what you want with all the cells that have been found, like:

MsgBox myString & " was found in " & d.Address(False, False)

End Sub

HTH,
Bernie
MS Excel MVP

"KR" wrote in message
...
I have a string in VBA (variable "mystring") and text strings in Sheet1,
column A (A2:A100). I need to identify (in VBA) whether "mystring" is
already in the range of A2:A100, but I will be repeating this operation
thousands of times, so I'm looking for something better than just
looping
A2:A100 and checking against each value individually, something more
like:

If mystring (is in) Sheet1.range("A2:A100") then....

but I can't find the right keyword- I've looked for "contains",
"includes",
"in", and others.... what is the proper way to write this?

Many thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.

As always, Tom, an excellent point.

Bernie

"Tom Ogilvy" wrote in message
...
Since he said:

right keyword- I've looked for "contains", "includes",
"in", and others....

you might want LookAt:=xlPart rather than LookAt:=xlWhole

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Keith,

If you could have multiples of the same value:

Sub FindValues2()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myString As String
Dim firstAddress As String

'This can also be set other ways

myString = InputBox("Enter the key word for search", , "Hello")

With Range("A2:A100")

Set c = .Find(myString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

'Then do what you want with all the cells that have been found, like:

MsgBox myString & " was found in " & d.Address(False, False)

End Sub

HTH,
Bernie
MS Excel MVP

"KR" wrote in message
...
I have a string in VBA (variable "mystring") and text strings in
Sheet1,
column A (A2:A100). I need to identify (in VBA) whether "mystring" is
already in the range of A2:A100, but I will be repeating this
operation
thousands of times, so I'm looking for something better than just
looping
A2:A100 and checking against each value individually, something more
like:

If mystring (is in) Sheet1.range("A2:A100") then....

but I can't find the right keyword- I've looked for "contains",
"includes",
"in", and others.... what is the proper way to write this?

Many thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't
represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.