Re: Searching into Non-Contiguous ranges in a columns
- From: Tom Ogilvy <TomOgilvy@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 15 Jul 2007 13:56:02 -0700
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
dguillett1@xxxxxxxxxxxxx
"Carlo Gonella" <cgonella@xxxxxxxx> wrote in message
news:469a1e7c$0$4797$4fafbaef@xxxxxxxxxxxxxxxxxxxxxx
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
- References:
- Searching into Non-Contiguous ranges in a columns
- From: Carlo Gonella
- Re: Searching into Non-Contiguous ranges in a columns
- From: Don Guillett
- Re: Searching into Non-Contiguous ranges in a columns
- From: Tom Ogilvy
- Searching into Non-Contiguous ranges in a columns
- Prev by Date: Re: Creating an array
- Next by Date: Re: Select Case syntax
- Previous by thread: Re: Searching into Non-Contiguous ranges in a columns
- Next by thread: RE: Searching into Non-Contiguous ranges in a columns
- Index(es):
Relevant Pages
|
