N
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
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