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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext and more than 1 Area
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] On Fri, 12 Feb 2010 11:00:01 -0800, Neal Zimm wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext and more than 1 Area
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 "Chip Pearson" wrote: 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] On Fri, 12 Feb 2010 11:00:01 -0800, Neal Zimm wrote: 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 . |
Reply |
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 |