Run this macro (tested) with the appropriate sheet being the active
sheet. It finds three such sequences:Sub blah()
'$B$1:$B$5,$A$6:$A$18,$B$19:$B$21,$C$22:$C$40 The shape of the
multi-area range to check.
AtLeastOneSequenceFound = False
With ActiveSheet
For Each cll In Intersect(.UsedRange, .Columns("AF"))
'On Error Resume Next '(uncomment if a #Value causes an error in
the next line)
If cll.Value = 1 And cll.Offset(1).Value = 2 Then 'a prelim search
for candidates before checking deeper.
'cll.Offset(,
-1).Range("$B$1:$B$5,$A$6:$A$18,$B$19:$B$21,$C$22:$ C$40").Select
'ActiveWindow.ScrollRow = cll.Row - 1
'Stop
SequenceFailed = False
For Each myArea In cll.Offset(,
-1).Range("$B$1:$B$5,$A$6:$A$18,$B$19:$B$21,$C$22:$ C$40").Areas
'myArea.Select
For i = 1 To myArea.Cells.Count
'myArea.Cells(i).Select
If myArea.Cells(i).Value < i Then
SequenceFailed = True
Exit For
End If
Next i
If SequenceFailed Then Exit For
Next myArea
If Not SequenceFailed Then
AtLeastOneSequenceFound = True
cll.Offset(,
-1).Range("$B$1:$B$5,$A$6:$A$18,$B$19:$B$21,$C$22:$ C$40").Select
ActiveWindow.ScrollRow = cll.Row - 1
MsgBox "Sequence found starting at " & cll.Address(False,
False)
'Stop
'Code here will copy but awaiting what to copy (a) if more than
one sequence found and
'(b) as there is no data in some of the cells to be copied,
what to copy? Both a and b apply on this sheet.
End If
End If 'cll.Value = 1 And cll.Offset(1) = 2
'On Error GoTo 0' cancels On Error Resume Next
Next cll
End With 'activesheet
If Not AtLeastOneSequenceFound Then MsgBox "Sequence not found"
End Subbut note:
1. You said:"1 thru 5 1 thru 13 1 thru 3 1 thru 19" but you also said
"AG1785:AG1804" was found for the last bit which is 1 thru *20*. Which
is it? I've assumed 19 above.
2. You said:"the values in ranges Z1764:Z1768, Y1769:Y1781, Z1782:Z1784
and AA1785:AA1804 respectively,must be copied". Some of these cells are
blank, viz. AA1785 and AA1786. Surely not your intention.
3. What to do when a second and subsequent sequence is found? (You
won't want to be copying values to AN2:AN41 again.)
--
p45cal
*p45cal*
------------------------------------------------------------------------
p45cal's Profile:
http://www.thecodecage.com/forumz/member.php?userid=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=125907