| Home |
| Search |
| Today's Posts |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching into Non-Contiguous ranges in a columns
Here are a couple of tweaks that allowed it to work with your data
Sub SearchinRanges() Application.ScreenUpdating = False Max = 0 slr = Cells(Rows.Count, "a").End(xlUp).Row mv = slr Do Until r2 = slr r1 = Columns(1).Find(What:="zzz", After:=Cells(mv, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row If Max < r1 Then Max = r1 r2 = Columns(1).Find(What:="yyy", After:=Cells(r1, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row If r2 <= Max Then Exit Do '======== mc = 0 With Range(Cells(r1, 1), Cells(r2, 1)) Set r = .Cells Set c = .Find(What:="12v", After:=Cells(r2, 1)) Debug.Print .Address If Not c Is Nothing Then firstAddress = c.Address Do If Trim(c.Offset(1)) = "12t" Then mc = mc + 1 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With MsgBox "in Range " & r.Address(0,0) & " Pairs found: " & mc mv = r1 Loop '========== End Sub -- Regards, Tom Ogilvy "Don Guillett" wrote: I did not open your file but this should do it using a combination of FIND to define the ranges and FINDNEXT to get the count Sub SearchinRanges() Application.ScreenUpdating = False mv = 1 slr = Cells(Rows.Count, "a").End(xlUp).Row Do Until r2 = slr r1 = Columns(1).Find(What:="zzz", After:=Cells(mv, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row r2 = Columns(1).Find(What:="zzz", After:=Cells(r1, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row '======== mc = 0 With Range(Cells(r1, 1), Cells(r2, 1)) Set c = .Find("12v", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do If c.Offset(1) = "12t " Then mc = mc + 1 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With MsgBox mc mv = r1 Loop '========== End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlo Gonella" wrote in message . .. Hi everyone. In the attachment you can see an easy example of what I need to have with the coding I can't actually figure out.. ( VIRUS FREE ). Hope someone can help ! Thanks in advance. --------------------------------------------------------------------------------------------------------------------------- Assuming I have a colum of codes Range("A:A") ... I know my codes are from A5 to A635. I also know there are some codes I can identify and which have relevant meaning in statistic analysis I have to do. I also know I need to analyze the codes between certain 'sequences'. i.e. : In the A5--A635 cells, I have 9 times the 'zzz' code. And in the same range, I have 8 times the 'yyy' code. I need to search this : How many times the code 12t comes exactly after the code 12v ( ie 12v in cell A7, 12t in cell A8 ) but ONLY when they are in a range delimited between 'zz'z and 'yyy' ? I meanm by example : zzz <--- BEGINNING OF THE RANGE 12r 12v <--- first condition ok, not the second, go on 12e 12g 12v <---- first condition ok 12t <---- secondo condition ok , --- ONE TIME 12e 12r 12t 12y 12v <--- first condition ok, not the second, go on yyy <--- END OF THE RANGE I WANT TO ANALYZE -- GO ON IN THE CODE AND SEARCH FOR THE NEXT ' zzz ' then analyze the range again... 12e 12k 12l 12m 12v <--- first condition ok, second too, but the macro doesn't care 12t <--- as it is searching the 'zzz' code, to analyze after that and before the next 'yyy' kkk 12r 12e 12r 12y 12b 12e 12p zzz <--- HERE WE ARE AGAIN !!! START ANALYZING FROM HERE ... 12r 12v <--- first condition ok 12t <--- secondo condition ok --- TWO TIMES .... and so on ... I hope it's clear and it's just something I miss... I can find anything I want to analyze... But not in multiple and non-contiguous ranges... Thank you for any hint. CarloG |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching into Non-Contiguous ranges in a columns
Here is an additional tweak
that should prevent an endless loop if the zzz - yyy paring is not down to the last filled cell in your column (A in this case). Sub SearchinRanges() Application.ScreenUpdating = False Max = 0 slr = Cells(Rows.Count, "a").End(xlUp).Row mv = slr Do Until r2 = slr r1 = Columns(1).Find(What:="zzz", After:=Cells(mv, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row If r1 <= Max Then Exit Do If Max < r1 Then Max = r1 r2 = Columns(1).Find(What:="yyy", After:=Cells(r1, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row If r2 <= Max Then Exit Do '======== mc = 0 With Range(Cells(r1, 1), Cells(r2, 1)) Set r = .Cells Set c = .Find(What:="12v", After:=Cells(r2, 1)) Debug.Print .Address If Not c Is Nothing Then firstAddress = c.Address Do If Trim(c.Offset(1)) = "12t" Then mc = mc + 1 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With MsgBox "in Range " & r.Address & " Pairs found: " & mc mv = r1 Loop '========== End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: Here are a couple of tweaks that allowed it to work with your data Sub SearchinRanges() Application.ScreenUpdating = False Max = 0 slr = Cells(Rows.Count, "a").End(xlUp).Row mv = slr Do Until r2 = slr r1 = Columns(1).Find(What:="zzz", After:=Cells(mv, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row If Max < r1 Then Max = r1 r2 = Columns(1).Find(What:="yyy", After:=Cells(r1, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row If r2 <= Max Then Exit Do '======== mc = 0 With Range(Cells(r1, 1), Cells(r2, 1)) Set r = .Cells Set c = .Find(What:="12v", After:=Cells(r2, 1)) Debug.Print .Address If Not c Is Nothing Then firstAddress = c.Address Do If Trim(c.Offset(1)) = "12t" Then mc = mc + 1 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With MsgBox "in Range " & r.Address(0,0) & " Pairs found: " & mc mv = r1 Loop '========== End Sub -- Regards, Tom Ogilvy "Don Guillett" wrote: I did not open your file but this should do it using a combination of FIND to define the ranges and FINDNEXT to get the count Sub SearchinRanges() Application.ScreenUpdating = False mv = 1 slr = Cells(Rows.Count, "a").End(xlUp).Row Do Until r2 = slr r1 = Columns(1).Find(What:="zzz", After:=Cells(mv, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row r2 = Columns(1).Find(What:="zzz", After:=Cells(r1, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row '======== mc = 0 With Range(Cells(r1, 1), Cells(r2, 1)) Set c = .Find("12v", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do If c.Offset(1) = "12t " Then mc = mc + 1 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With MsgBox mc mv = r1 Loop '========== End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlo Gonella" wrote in message . .. Hi everyone. In the attachment you can see an easy example of what I need to have with the coding I can't actually figure out.. ( VIRUS FREE ). Hope someone can help ! Thanks in advance. --------------------------------------------------------------------------------------------------------------------------- Assuming I have a colum of codes Range("A:A") ... I know my codes are from A5 to A635. I also know there are some codes I can identify and which have relevant meaning in statistic analysis I have to do. I also know I need to analyze the codes between certain 'sequences'. i.e. : In the A5--A635 cells, I have 9 times the 'zzz' code. And in the same range, I have 8 times the 'yyy' code. I need to search this : How many times the code 12t comes exactly after the code 12v ( ie 12v in cell A7, 12t in cell A8 ) but ONLY when they are in a range delimited between 'zz'z and 'yyy' ? I meanm by example : zzz <--- BEGINNING OF THE RANGE 12r 12v <--- first condition ok, not the second, go on 12e 12g 12v <---- first condition ok 12t <---- secondo condition ok , --- ONE TIME 12e 12r 12t 12y 12v <--- first condition ok, not the second, go on yyy <--- END OF THE RANGE I WANT TO ANALYZE -- GO ON IN THE CODE AND SEARCH FOR THE NEXT ' zzz ' then analyze the range again... 12e 12k 12l 12m 12v <--- first condition ok, second too, but the macro doesn't care 12t <--- as it is searching the 'zzz' code, to analyze after that and before the next 'yyy' kkk 12r 12e 12r 12y 12b 12e 12p zzz <--- HERE WE ARE AGAIN !!! START ANALYZING FROM HERE ... 12r 12v <--- first condition ok 12t <--- secondo condition ok --- TWO TIMES .... and so on ... I hope it's clear and it's just something I miss... I can find anything I want to analyze... But not in multiple and non-contiguous ranges... Thank you for any hint. CarloG |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Non contiguous ranges | Excel Programming | |||
| Copying non-contiguous columns to contiguous columns | Excel Programming | |||
| vlookup on non-contiguous ranges | Excel Worksheet Functions | |||
| summing non contiguous ranges | Excel Discussion (Misc queries) | |||
| sorting non contiguous ranges | Excel Discussion (Misc queries) | |||
Linear Mode
