using search function in VBA

F

firsttimevba

Hi,
I'm very new to VBA. sorry for asking dumb question. I'm trying to do the
below and it is not working.

x = 500


Do While Cells(x, 2).Value <> ""

y = 100

Do While Cells(y, 5).Value <> ""

if worksheetfunction.search(cells(x, 2), cells(y, 5)) > 0 then
' do something
end if
y = y + 1
loop
x = x + 1
loop

Here I want to search the text string of each cell from one range into
another range. How do I do this. The above code compiles fine. But I'm
getting a runtime error on the line "if worksheetfunction.search(cells(x, 2),
cells(y, 5)) > 0 then". Looks like the search function can not take variable
such as x and y.
Thanks for any help!
 
R

Ryan H

I think this is what you are wanting. Currently, the code will search all
the data you have in Col. A in Range("B1:B100"). You may need to tweak it to
fit your application. Let me know if I can help more. Hope this helps! If
so, let me know, click "YES" below.

Option Explicit

Sub SearchRange()

Dim lngLastRow As Long
Dim rngMyRange As Range
Dim rngFound As Range
Dim rng As Range

' find last cell with data in Col.A
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

' range you want to find stuff in
Set rngMyRange = Range("B1:B100")

' go thru each cell in
For Each rng In Range("A1:A" & lngLastRow)
Set rngFound = rngMyRange.Find(What:=rng.Text, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True, _
SearchFormat:=False)
If Not rngFound Is Nothing Then
MsgBox "Found " & rng.Value & " in " & rngFound.Address
' do something if found
Else
MsgBox "Didn't find anything."
' do this if not found
End If
Next rng

End Sub
 
F

firsttimevba

Hi Ryan,

thanks so much. It is almost working except that the FIND function is
trying to find an identical match (whole word). What I'm looking for is a
substring match. For example, a match if "abc" is found inside "abcdef" and I
will want to update a write the abcdef to another cell. I can try to dig it
up to see how to do some type of wildcard matching. But if you can give me
another hint I will appreciate a lot.

Thank you
Charles
 
R

Rick Rothstein

Change this part of the Find method...

LookAt:=xlWhole

to this...

LookAt:=xlPart

By the way, all the possible arguments for the Find function are shown in
the help files for it. Just put your text cursor on or next to the word find
(in your code) and press F1... you want the section that applies to the
Range object.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top