Search string in a range

[ThrottleWorks]

Hi,

I need to find a string in a range. Please note, I will upload sample file shortly.
Sorry for not uploading at present.

I have 2 ranges.

Range 1 will always be Cell A2, value of range A2 (for example) is "as honda sa"

Range 2 is list of names, for example say bikes.
1 Honda
2 Yamaha
3 BMW
4 KTM
5 SUZUKI

I was thinking of a loop with Range 2 as base and search values in range A2.
So loop will first pick Honda and will search if it is present in value of Cell A2.

I thought of using LookAt:=xlPart while searching but I am not sure where it will pick right results always.

Can anyone please suggest me a better solution.

 

[Luke M]

Are we checking if Range1 is somewhere in Range2, or are we checking each cell in Range2 if it matches Range1?

Perhaps this gives you a good example?

Sub ExampleSearch()
Dim rng1 As Range
Dim rng2 As Range
Dim fCell As Range

'Where are the ranges?
Set rng1 = Range("A2")
Set rng2 = Range("B1:B10")

'Find the item
Set fCell = rng2.Find(what:=rng1.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

If fCell Is Nothing Then
    MsgBox "Value not found"
Else
    MsgBox "Found in cell: " & fCell.Address(False, False)
End If

End Sub

 

[ThrottleWorks]

Hi Luke Sir, thanks a lot for the help.

We need to check if Range A2 value is (partially) present in D Column.

Range A2 value will consist of key words mentioned in D Column.
In our example "as honda as" has a kew word honda.

I need to pick this honda key word from D Column.

The reason, I need to peform further actions based on the key word identified.
To simplfy, if Range A2 value has key word as honda then perform action 1 if ktm action 2 and so on.

I am checking the code provided by you.

Good night.

 

[Luke M]

For a keyword type search then, I'd go this route:

Sub ExampleSearch2()
Dim rng1 As Range
Dim rng2 As Range
Dim c As Range

'Where are the ranges?
Set rng1 = Range("A2")
Set rng2 = Range("B1:B10")

For Each c In rng2
    If InStr(1, rng1.Value, c.Value, vbTextCompare) Then
        MsgBox "Keyword found: " & c.Value
        Exit Sub
    End If
Next c
MsgBox "Value not found"
End Sub

 

[ThrottleWorks]

Hi Luke Sir, thanks a lot for the help !

Your previous code is working perfect however I am trying the latest code provided by you and share the results shorlty.

Once again thanks a lot !

 

[ThrottleWorks]

Hi @Luke M Sir, it's working perfect !

I guess the line "vbTextCompare" you have used makes the difference and makes it more safe.

Good night.

 

[Luke M]

Glad to help. The TextCompare operator makes sure that the Instr method is not case sensitive. Let me know if you run into any other questions.