using search function in VBA

[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!

 

[joel]

Rather than solve the problem I think you should look at Chip Pearson'
wesite. It will give you a lot of answers and good examples

'CPearson.com Topic Index' (http://www.cpearson.com/Excel/topic.aspx

 

[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

 

[Peter T]

Fully describe what you want to do (don't include code)

Regards,
Peter T

 

[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

 

[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.