FindNext and more than 1 Area

[Neal Zimm]

Hi All,
Does the microsoft help example re: .Find and .FindNext need modification
when the range being searched contains more than 1 area ?

The help makes no specific mention of this, but my answer to the
above question is yes. The code below did NOT work as expected in
all of my test cases.

The VBA below are the germaine parts of a general function I've built. In
addition to acting as a 'pure' .Find, I want this function to return more
than one cell when:
1. More than 1 cell is an App 'error' condition, lDupeQty is > 0.
2. More than 1 cell is OK, lDupeQty will be >= 2.

The comments near .FindNext give details on the testing, and the problem
which is an inconsistant wrap around back to LookAfterRng when InRng has
more than 1 area.

Thanks,
Neal Z.


Public Function Rng_vFindsValF(InRng As Range, sLookFor As String, _
Optional LookAfterRng As Range = Nothing, _
Optional lDupeQty As Long = 0, Optional FirFindRng As Range = Nothing, _
Optional bxlWhole As Boolean = True, _
Optional bLookInValues As Boolean = True, _
Optional bDebugPrt As Boolean = False) As Range

'Outputs: Return a range of found sLookFor's. Range will include
'duplicate' values. Meaning of lDupeQty output value will vary
'with input parm values re qty of expected finds and the value
'in the LookAfterRng input parm. FirFindRng is the 1st range
'found.

Dim statements not shown.
Statements valuing iLookIn and iWholeOrPart via optional parms not shown,
but work.
Statements setting LookAfterRng when input parm is nothing, not shown but
they work as expected.

The Debug.Print Message paragraph, not shown, but works ok.


With InRng
Set WorkRng = .Find(sLookFor, LookAfterRng, iLookIn, iWholeOrPart)
If Not WorkRng Is Nothing Then

Set FirFindRng = WorkRng ' The 1st find
Set DupeRng = WorkRng
FirstAdr = FirFindRng.Address
If FirstAdr <> LookAfterRng.Address Then lDupeQty = 1

'InRng is "$E$17:$E$33,$M$18" LookAfterRng is $M$18
Do
'cells are general format, contain 8 digit number.
'test 1, OK, same values in E17 and M18, wrapped back to M18.
'test 2, ??, same values in E23 and M18, NO wrap back to M18.
'It's a mystery why test 2 does not work, E17 E23 are in
'the same area. In the sub calling this function, my interim
'workaround is to set InRng at $E$17:$M$33". Test 2 then works
'and luckily, data in columns F-L won't be found accidentally.

Set WorkRng = .FindNext(WorkRng)

If Not WorkRng Is Nothing And WorkRng.Address <> FirstAdr Then
lDupeQty = lDupeQty + 1
Set DupeRng = Union(DupeRng, WorkRng)
Else
End If
Loop While Not WorkRng Is Nothing And WorkRng.Address <> FirstAdr

Set Rng_vFindsValF = DupeRng

If Not Intersect(DupeRng, LookAfterRng) Is Nothing Then _
lDupeQty = lDupeQty - 1

If bDebugPrt Then
sMisc = "Returned Func Rng: " & DupeRng.Address
GoSub Message
End If

ElseIf bDebugPrt Then
sMisc = "Returned Func Rng: Nothing"
GoSub Message
End If
End With

 

[Chip Pearson]

Find and FindNext work with ranges that are comprised of more than one
Area. I have on my web site a function called FindAll that finds all
occurrences of a value in a range and returns a range object composed
of the found cells. It works fine with mutliple areas. In the initial
Find method call, you need to specify the After parameter to be a cell
whose row and column number are greater than that of any cell in any
area.

See www.cpearson.com/Excel/FindAll.aspx for code and examples.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Neal Zimm]

Thanks much Chip. Some follow up questions, please.

1) I took a quick look at your site and found the FindAll addin. Is this
what you mean by your function ?

2) When you say "you need to specify the After parameter to be a cell
whose row and column number are greater than that of any cell in any
area", does this pertain to code in general, or to your addin/function ?

2a) My example area is E17:E33,M18 row 33 is largest, but the M
column is greatest, what do you specify in this situation ?

3) I did not show all of the comments in my function (muddy the water too
much, I thought). In trying to use my function as a general tool, when the
LookAfterRng is nothing upon input, some of the code you didn't see scans the
areas and sets it to the largest row#, with the highest column # to start the
search "at the top" when I'm looking for all values.

4) If i'm using the function to find duplicate values, where more than 1
is an error condition in my App, I set the LookAfterRng to the cell with the
value. Then, if the first find is NOT the LookAfterRng, I have a "dupe" which
is bad. In this case I cannot set LookAfter.. to the bottom right cell.
Sounds like I may need more than one function, rather than having one do
it all.

I'll check out your addin in detail very soon.

Thanks again,
Neal






--
Neal Z

Find and FindNext work with ranges that are comprised of more than one
Area. I have on my web site a function called FindAll that finds all
occurrences of a value in a range and returns a range object composed
of the found cells. It works fine with mutliple areas. In the initial
Find method call, you need to specify the After parameter to be a cell
whose row and column number are greater than that of any cell in any
area.

See www.cpearson.com/Excel/FindAll.aspx for code and examples.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hi All,
Does the microsoft help example re: .Find and .FindNext need modification
when the range being searched contains more than 1 area ?

The help makes no specific mention of this, but my answer to the
above question is yes. The code below did NOT work as expected in
all of my test cases.

The VBA below are the germaine parts of a general function I've built. In
addition to acting as a 'pure' .Find, I want this function to return more
than one cell when:
1. More than 1 cell is an App 'error' condition, lDupeQty is > 0.
2. More than 1 cell is OK, lDupeQty will be >= 2.

The comments near .FindNext give details on the testing, and the problem
which is an inconsistant wrap around back to LookAfterRng when InRng has
more than 1 area.

Thanks,
Neal Z.


Public Function Rng_vFindsValF(InRng As Range, sLookFor As String, _
Optional LookAfterRng As Range = Nothing, _
Optional lDupeQty As Long = 0, Optional FirFindRng As Range = Nothing, _
Optional bxlWhole As Boolean = True, _
Optional bLookInValues As Boolean = True, _
Optional bDebugPrt As Boolean = False) As Range

'Outputs: Return a range of found sLookFor's. Range will include
'duplicate' values. Meaning of lDupeQty output value will vary
'with input parm values re qty of expected finds and the value
'in the LookAfterRng input parm. FirFindRng is the 1st range
'found.

Dim statements not shown.
Statements valuing iLookIn and iWholeOrPart via optional parms not shown,
but work.
Statements setting LookAfterRng when input parm is nothing, not shown but
they work as expected.

The Debug.Print Message paragraph, not shown, but works ok.


With InRng
Set WorkRng = .Find(sLookFor, LookAfterRng, iLookIn, iWholeOrPart)
If Not WorkRng Is Nothing Then

Set FirFindRng = WorkRng ' The 1st find
Set DupeRng = WorkRng
FirstAdr = FirFindRng.Address
If FirstAdr <> LookAfterRng.Address Then lDupeQty = 1

'InRng is "$E$17:$E$33,$M$18" LookAfterRng is $M$18
Do
'cells are general format, contain 8 digit number.
'test 1, OK, same values in E17 and M18, wrapped back to M18.
'test 2, ??, same values in E23 and M18, NO wrap back to M18.
'It's a mystery why test 2 does not work, E17 E23 are in
'the same area. In the sub calling this function, my interim
'workaround is to set InRng at $E$17:$M$33". Test 2 then works
'and luckily, data in columns F-L won't be found accidentally.

Set WorkRng = .FindNext(WorkRng)

If Not WorkRng Is Nothing And WorkRng.Address <> FirstAdr Then
lDupeQty = lDupeQty + 1
Set DupeRng = Union(DupeRng, WorkRng)
Else
End If
Loop While Not WorkRng Is Nothing And WorkRng.Address <> FirstAdr

Set Rng_vFindsValF = DupeRng

If Not Intersect(DupeRng, LookAfterRng) Is Nothing Then _
lDupeQty = lDupeQty - 1

If bDebugPrt Then
sMisc = "Returned Func Rng: " & DupeRng.Address
GoSub Message
End If

ElseIf bDebugPrt Then
sMisc = "Returned Func Rng: Nothing"
GoSub Message
End If
End With

.