Easy newbie question

[KR]

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

 

[Tom Ogilvy]

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

 

[Bernie Deitrick]

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

 

[Tom Ogilvy]

Since he said:

right keyword- I've looked for "contains", "includes",

"in", and others....

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

 

[Bernie Deitrick]

As always, Tom, an excellent point.

Bernie

Since he said:

"in", and others....

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