Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext and more than 1 Area
Hi All,
Does the microsoft help example .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 -- Neal Z |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do I need Findnext? Not sure how | Excel Programming | |||
Findnext | Excel Discussion (Misc queries) | |||
loop and findnext | Excel Programming | |||
FindNext | Excel Programming | |||
FindNext | Excel Programming |