Re: Searching into Non-Contiguous ranges in a columns

Tech-Archive recommends: Fix windows errors by optimizing your registry

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



.

Relevant Pages

  • Findnext in Fuction/Sub
    ... Als Sub funktioniert alles ohne Probleme: ... Dim sel As Range ... Dim firstAddress As String ... Dim MyCount As Integer ...
    (microsoft.public.de.excel)
  • RE: Find method fails in hidden range
    ... Sub HideProjectRows() ... Dim Str As String, FirstAddress As String ...
    (microsoft.public.excel.programming)
  • Re: Find next cell that contains
    ... Sub SEARCHNEXT() ... Loop While Not c Is Nothing And c.Address firstAddress ... Don Guillett ... Microsoft MVP Excel ...
    (microsoft.public.excel.programming)
  • Re: Place formula in cells
    ... "Don Guillett" wrote: ... Sub makeforulasifFormula() 'Me ... Loop While Not c Is Nothing And c.Address firstAddress ... asterisk is found on row 15. ...
    (microsoft.public.excel.programming)
  • Re: Conditional Formatting for an unknown range
    ... Sub findstops() ... Select Case colorcount ... startrow = c.Row + 1 ... Loop While Not c Is Nothing And c.Address firstAddress ...
    (microsoft.public.excel.misc)