using search function in VBA
 

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!

using search function in VBA
 

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=163425

Microsoft Office Help

using search function in VBA
 

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
--
Cheers,
Ryan


"firsttimevba" wrote:

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!

using search function in VBA
 

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

Regards,
Peter T


"firsttimevba" wrote in message
...
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!

using search function in VBA
 

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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"firsttimevba" wrote:

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!

using search function in VBA
 

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.

--
Rick (MVP - Excel)


"firsttimevba" wrote in message
...
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

"Ryan H" wrote:

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
--
Cheers,
Ryan


"firsttimevba" wrote:

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!