Find Method vs. For...Next Loop
 

Greetings,
I'm trying to find text in a range. I can do it with a For...Next
Loop, but when I try it with the Find Method, it returns "Nothing."
I've gone over the help file until my eyes started to cross, but I
can't seem to figure out why it wouldn't work. Here's what's working
and what isn't:

---------------------
Dim ToBk As Workbook, FrBk As Workbook, ToSht As Worksheet, FrSht As
Worksheet
Dim ToCl As Range, FrCl As Range, RowNum As Integer, ColNum As
Integer, NumRows As Integer, NumCols As Integer
Dim AppNum As Integer, Cl As Range, LkRng As Range

<snip

AppNum = 172

Set LkRng = FrSht.UsedRange
RowNum = LkRng.Cells.SpecialCells(xlCellTypeLastCell).Row
Set LkRng = FrSht.Range(Cells(1, 2), Cells(RowNum, 2))

<-------------- This routine works correctly -------------------------

For Each Cl In LkRng
If Cl.Value Like "*" & AppNum & "*" Then
Debug.Print Cl.Value & vbLf & Cl.Address
End If
Next Cl

<--------------- This returns Nothing
----------------------------------
Set Cl = LkRng.Find(AppNum, LkRng(1), xlValues, xlWhole, xlByRows,
xlNext, False, False)
Debug.Print Cl.Value & vbLf & Cl.Address

---------------------------

The For...Next Loop returns the correct cell in the range. However,
the Find Method does not return anything. I'd prefer to do this in
one line of code rather than a loop, so if anyone can see what's wrong
with the Find, I'd really appreciate the advice.

XL2003 SP2. This is from a Sub procedure, not a Function procedure (I
learned that Find doesn't work in UDFs from a post back in 2002).

Thanks,
Mike

Find Method vs. For...Next Loop
 

Try changing xlWhole to xlPart to match your Like Statement.

--
Regards,
Tom Ogilvy



"mikelee101" wrote:

Greetings,
I'm trying to find text in a range. I can do it with a For...Next
Loop, but when I try it with the Find Method, it returns "Nothing."
I've gone over the help file until my eyes started to cross, but I
can't seem to figure out why it wouldn't work. Here's what's working
and what isn't:

---------------------
Dim ToBk As Workbook, FrBk As Workbook, ToSht As Worksheet, FrSht As
Worksheet
Dim ToCl As Range, FrCl As Range, RowNum As Integer, ColNum As
Integer, NumRows As Integer, NumCols As Integer
Dim AppNum As Integer, Cl As Range, LkRng As Range

<snip

AppNum = 172

Set LkRng = FrSht.UsedRange
RowNum = LkRng.Cells.SpecialCells(xlCellTypeLastCell).Row
Set LkRng = FrSht.Range(Cells(1, 2), Cells(RowNum, 2))

<-------------- This routine works correctly -------------------------

For Each Cl In LkRng
If Cl.Value Like "*" & AppNum & "*" Then
Debug.Print Cl.Value & vbLf & Cl.Address
End If
Next Cl

<--------------- This returns Nothing
----------------------------------
Set Cl = LkRng.Find(AppNum, LkRng(1), xlValues, xlWhole, xlByRows,
xlNext, False, False)
Debug.Print Cl.Value & vbLf & Cl.Address

---------------------------

The For...Next Loop returns the correct cell in the range. However,
the Find Method does not return anything. I'd prefer to do this in
one line of code rather than a loop, so if anyone can see what's wrong
with the Find, I'd really appreciate the advice.

XL2003 SP2. This is from a Sub procedure, not a Function procedure (I
learned that Find doesn't work in UDFs from a post back in 2002).

Thanks,
Mike

Find Method vs. For...Next Loop
 

On Jul 10, 12:40*pm, Tom Ogilvy
wrote:
Try changing *xlWhole to xlPart to match your Like Statement.

--
Regards,
Tom Ogilvy



"mikelee101" wrote:
Greetings,
I'm trying to find text in a range. *I can do it with a For...Next
Loop, but when I try it with the Find Method, it returns "Nothing."
I've gone over the help file until my eyes started to cross, but I
can't seem to figure out why it wouldn't work. *Here's what's working
and what isn't:

---------------------
Dim ToBk As Workbook, FrBk As Workbook, ToSht As Worksheet, FrSht As
Worksheet
Dim ToCl As Range, FrCl As Range, RowNum As Integer, ColNum As
Integer, NumRows As Integer, NumCols As Integer
Dim AppNum As Integer, Cl As Range, LkRng As Range

<snip

AppNum = 172

Set LkRng = FrSht.UsedRange
RowNum = LkRng.Cells.SpecialCells(xlCellTypeLastCell).Row
Set LkRng = FrSht.Range(Cells(1, 2), Cells(RowNum, 2))

<-------------- *This routine works correctly -------------------------

For Each Cl In LkRng
* * If Cl.Value Like "*" & AppNum & "*" Then
* * * * Debug.Print Cl.Value & vbLf & Cl.Address
* * End If
Next Cl

<--------------- *This returns Nothing
----------------------------------
Set Cl = LkRng.Find(AppNum, LkRng(1), xlValues, xlWhole, xlByRows,
xlNext, False, False)
Debug.Print Cl.Value & vbLf & Cl.Address

---------------------------

The For...Next Loop returns the correct cell in the range. *However,
the Find Method does not return anything. *I'd prefer to do this in
one line of code rather than a loop, so if anyone can see what's wrong
with the Find, I'd really appreciate the advice.

XL2003 SP2. *This is from a Sub procedure, not a Function procedure (I
learned that Find doesn't work in UDFs from a post back in 2002).

Thanks,
Mike- Hide quoted text -

- Show quoted text -

Duh...

I kind of figured I was overlooking something painfully obvious, but
didn't figure it'd be that bad.

Thanks for having better eyes than me.

Mike

Find Method vs. For...Next Loop
 

On Jul 10, 12:40*pm, Tom Ogilvy
wrote:
Try changing *xlWhole to xlPart to match your Like Statement.

--
Regards,
Tom Ogilvy



"mikelee101" wrote:
Greetings,
I'm trying to find text in a range. *I can do it with a For...Next
Loop, but when I try it with the Find Method, it returns "Nothing."
I've gone over the help file until my eyes started to cross, but I
can't seem to figure out why it wouldn't work. *Here's what's working
and what isn't:

---------------------
Dim ToBk As Workbook, FrBk As Workbook, ToSht As Worksheet, FrSht As
Worksheet
Dim ToCl As Range, FrCl As Range, RowNum As Integer, ColNum As
Integer, NumRows As Integer, NumCols As Integer
Dim AppNum As Integer, Cl As Range, LkRng As Range

<snip

AppNum = 172

Set LkRng = FrSht.UsedRange
RowNum = LkRng.Cells.SpecialCells(xlCellTypeLastCell).Row
Set LkRng = FrSht.Range(Cells(1, 2), Cells(RowNum, 2))

<-------------- *This routine works correctly -------------------------

For Each Cl In LkRng
* * If Cl.Value Like "*" & AppNum & "*" Then
* * * * Debug.Print Cl.Value & vbLf & Cl.Address
* * End If
Next Cl

<--------------- *This returns Nothing
----------------------------------
Set Cl = LkRng.Find(AppNum, LkRng(1), xlValues, xlWhole, xlByRows,
xlNext, False, False)
Debug.Print Cl.Value & vbLf & Cl.Address

---------------------------

The For...Next Loop returns the correct cell in the range. *However,
the Find Method does not return anything. *I'd prefer to do this in
one line of code rather than a loop, so if anyone can see what's wrong
with the Find, I'd really appreciate the advice.

XL2003 SP2. *This is from a Sub procedure, not a Function procedure (I
learned that Find doesn't work in UDFs from a post back in 2002).

Thanks,
Mike- Hide quoted text -

- Show quoted text -

Hello Tom,
Actually, I originally had xlPart in there. I changed it to xlWhole
just to see what would happen and must not have changed it back before
I copied and pasted.

However, I think I now know what's causing the failure, but I'm still
unclear on why it fails. The cell that I'm looking for in my test
data is B106. However, B106 is part of a range of merged cells,
B106:Q106. If I go in and manually unmerge the cells, then the find
method works correctly.

So, now I just need to decide if it's worth trying to programmatically
unmerge cells without losing too much formatting or just going with
the loop. However, I'm curious why the Find method wouldn't work with
a merged range if the What:= was in the upper left cell. Any idea if
this was by design?

Thank you very much for the help...

Mike