Hi:

This is the first time I am posting to this newsgroup and if it is not the
right place for my question, please point me to the correct newsgroup.

If I have three columns with a multiple sequences of numbers, separated by
empty cells,
is it programmatically possible to have a macro to select a specific pattern
in the three columns that meet the following conditions:

For example using columns R, S and T.

In column S, find a vertical sequence of 1, 2, 3, 4 and 5, that is followed
by a vertical sequence of

1 through 13, directly to the left of column S, the next row down in column
R, followed by a vertical

sequence of 1, 2, 3, directly to the right of column R, the next row down in
column S, and finally

followed by a vertical sequence of 1 through 19, directly to the right of
column S, the next row down

in column T?

My goal is to have a macro to find the right sequence of 40 numbers while
ignoring sequences that don't

match. A nice bonus would be if no matches are found after the search
completes, to display a "Match not found", messagebox!

Any help would be appreciated!
Thanks in advance,
Cecil

Yes, it is possible. But before I suggest a solution I'm a bit worried
by separated by empty cellsWhat is separated by empty cells? the sequences, or the individual
values, just one empty cell in each case, or several?
It would help if you could include an attachment (at codecage.com where
I'm answering this) preferably with the sequence searched for present so
I can test.


--
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

It would be best to post some lines of the data so we can see the pattern.
there are many was of searching through data like this and things that you
may not think are important may require different programming techniques. I
have done thousand of programs like this and each one has some unique
requirements. it is better to see samples of the data than a description. A
picture is worth a thousand words.

"Cecil" wrote:

Hi:

This is the first time I am posting to this newsgroup and if it is not the
right place for my question, please point me to the correct newsgroup.

If I have three columns with a multiple sequences of numbers, separated by
empty cells,
is it programmatically possible to have a macro to select a specific pattern
in the three columns that meet the following conditions:

For example using columns R, S and T.

In column S, find a vertical sequence of 1, 2, 3, 4 and 5, that is followed
by a vertical sequence of

1 through 13, directly to the left of column S, the next row down in column
R, followed by a vertical

sequence of 1, 2, 3, directly to the right of column R, the next row down in
column S, and finally

followed by a vertical sequence of 1 through 19, directly to the right of
column S, the next row down

in column T?

My goal is to have a macro to find the right sequence of 40 numbers while
ignoring sequences that don't

match. A nice bonus would be if no matches are found after the search
completes, to display a "Match not found", messagebox!

Any help would be appreciated!
Thanks in advance,
Cecil

Cecil;454870 Wrote:
Hi:

This is the first time I am posting to this newsgroup and if it is not
the
right place for my question, please point me to the correct newsgroup.

If I have three columns with a multiple sequences of numbers, separated
by
empty cells,
is it programmatically possible to have a macro to select a specific
pattern
in the three columns that meet the following conditions:

For example using columns R, S and T.

In column S, find a vertical sequence of 1, 2, 3, 4 and 5, that is
followed
by a vertical sequence of

1 through 13, directly to the left of column S, the next row down in
column
R, followed by a vertical

sequence of 1, 2, 3, directly to the right of column R, the next row
down in
column S, and finally

followed by a vertical sequence of 1 through 19, directly to the right
of
column S, the next row down

in column T?

My goal is to have a macro to find the right sequence of 40 numbers
while
ignoring sequences that don't

match. A nice bonus would be if no matches are found after the search
completes, to display a "Match not found", messagebox!

Any help would be appreciated!
Thanks in advance,
Cecil


Hi,

Thank you for your willingness to help me!
Sorry about my lack of clearity. As for the number of blank cells in
the columns of sequences,
the number of blank cells can vary speratically before consistant
sequences begin to develop.
I have included an example worksheet in a workbook to serve as an
example.
The columns of sequences in question in this worksheet are AE, AF and
AG.
I used columns R, S and T in my post to try and minimize the complexity
in explaining my problem.
You'll notice that ranges in columns AF1764:AF1768, AE1769:AE1781,
AF1782:AF1784 and AG1785:AG1804
actually match the sequence I am seeking.
Hence the target sequence of 40 data points as follows;

1 thru 5 1 thru 13 1 thru 3 1 thru 19
AF1764:AF1768, AE1769:AE1781, AF1782:AF1784 and AG1785:AG1804

My final task will be to copy the relative columns of values from
columns Y, Z and AA, into column AN2:AN41, or display a message box
stating the "Sequence not Found" using the macro.
Therefore, the values in ranges Z1764:Z1768, Y1769:Y1781, Z1782:Z1784
and AA1785:AA1804 respectively,
must be copied to range AN2:AN41 for export into other applications.

Thank you again for your help,
Cecil


+-------------------------------------------------------------------+
|Filename: Multi-SequenceSelection.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=219|
+-------------------------------------------------------------------+

--
c-town
------------------------------------------------------------------------
c-town's Profile: http://www.thecodecage.com/forumz/member.php?userid=695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125907

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

p45cal;455194 Wrote:
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.)
4. There are a bunch of commented-out lines in the code which I used to
help me develop/debug so try reinstating some and when the code stops,
step through with the F8 and F5 keys.

Hi p45cal,

You are correct about the 1 thru 19 sequence, rather than 1 thru 20.
The total number of cells in the combined sequences targeted should
only be 40.

Your comment, “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”. Is also correct, if possible via code, I would like to have the search
continue on to the group of sequences that do not have such flaws, but
with the intent of capturing the first correct sequence found.

While reading through your code I couldn’t help but notice your use of
cell “c11". Please tell me a bit of it’s function in case I need to
adjust it’s address or target value for my worksheet?
I’ve created the module with your code, and adjusted the column
addresses to match my worksheet, but I am a bit vague on the function of
cell “c11”.

Thank you so much, for your help thus far. And I really appreciate your
time.
Cecil,


--
c-town
------------------------------------------------------------------------
c-town's Profile: http://www.thecodecage.com/forumz/member.php?userid=695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125907

*1.* Speaking of flaws, that portion of the sequence should not have passed
as a numerically consecutive set of values.
Why not? Is it something we should code to avoid? What defines it as
failing?


*2.* You didn't address Q3 (What to do when a second and subsequent
sequence is found? (You won't want to be copying values to AN2:AN41
again.))


*3.* While reading through your code I couldn’t help but notice your use of
cell “c11". Please tell me a bit of it’s function
Careful here, it's not “c11", but "cll" (to use capitals:it's not "C11"
but "CLL"). It's a range object which I chose to name 'cll' and refers
to a single cell, set in the line:For Each cll In
Intersect(.UsedRange, .Columns("AF"))which, if I was being
explicit in the code, could have read:For Each cll In
Intersect(.UsedRange, .Columns("AF")).CellsIt's just a way
of running down each cell in column AF. If you had just this code in a
sub and stepped through it with F8:For Each cll In
Intersect(.UsedRange, .Columns("AF")).Cells
cll.Select
Next cll
you could watch it select each cell in turn, starting at
the top.


--
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

p45cal;456352 Wrote:
1.
Why not? Is it something we should code to avoid? What defines it as
failing?


2.[/b] YOU DIDN'T ADDRESS Q3 (WHAT TO DO WHEN A SECOND AND SUBSEQUENT
SEQUENCE IS FOUND? (YOU WON'T WANT TO BE COPYING VALUES TO AN2:AN41
AGAIN.))


[b]3.
Careful here, it's not “c11", but "cll" (to use capitals:it's not "C11"
but "CLL"). It's a range object which I chose to name 'cll' and refers
to a single cell, set in the line:For Each cll In
Intersect(.UsedRange, .Columns("AF"))which, if I was being
explicit in the code, could have read:For Each cll In
Intersect(.UsedRange, .Columns("AF")).CellsIt's just a way
of running down each cell in column AF. If you had just this code in a
sub and stepped through it with F8:For Each cll In
Intersect(.UsedRange, .Columns("AF")).Cells
cll.Select
Next cll
you could watch it select each cell in turn, starting at
the top.

ps. it might be a good idea to attach your amended workbook if you want
me to add code

Hi p45cal,

I want to respond to your recent questions and add a few update
comments.

but note:
Q1. 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.

A1) You are correct about the 1 thru 19 sequence, rather than 1 thru
20.
The total number of cells in the combined sequences targeted should
only be 40.

Your comment,

“Q2. 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”.


A2) Is also correct, if possible via code, I would like to have the
search continue on to the group of sequences that do not have such
flaws, but with the intent of capturing the first correct group of
sequences found.
Speaking of flaws, that portion of the sequence should not have passed
as a numerically consecutive set of values based on my column formulas.


With respect to question #2, I realize now that I must have a final
test in the macro for it to be completed successfully. Because the goal
of generating this sequence of "1 thru 5, 1 thru 13, 1 thru 3, 1 thru
19", is to locate, and confirm that a specific set of values exist, and
meet a flow of continuous values in 40 points of data that create the
sequence form.

Q3. What to do when a second and subsequent sequence is found? (You
won't want to be copying values to AN2:AN41 again.)

A3) Briefly put, the job of the formulas in columns AE, AF and AG, is
to monitor values six columns to the left in parallel cells of columns
Y, Z and AA. In turn, the rudimentary sequence of numbers are generated
revealing the target form from the data stream.
Therefore, once the correct sequence is located in columns AE, AF and
AG, a final test would be that the copy process should only succeed if
values truly exist in the cells being copied. So, once a match is found,
then what is needed is to test the authenticity of cell values and
perform the copy process and stop the search.

The position of where each sequence begins and ends in relation to
one-another, is critical to the overall function and results of the
worksheet. More specifically, the form that is created by the target
combination of sequences is most critical. Values in a cell that are
extended after, or next to the form of the target combination of
sequences should be ignored by the macro.

I have since adjusted the formulas in “AG and AA” columns to correct
the oversight that allowed blank cells to be represented as having
useable values, thanks to your attention to details.

I greatly appreciate how far we have gotten already with your help!
Although, currently the macro is searching the wrong columns, “BI, BJ
and BK”.
But I have been unable to decipher where in the code, the start point
for the search is being set. So I have been unable to adjust it.
So I am including my current version of the worksheet, in hopes that
you can correct it.
Other than that, I have the full macro commands active with no program
errors or error trapping commands active, just the Message Box stating
"Sequence not found" and displaying the active cell address when the
macro ends.

Thanks again p45cal, for any help you may offer!
Cecil


+-------------------------------------------------------------------+
|Filename: Multi-SequenceSelection.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=232|
+-------------------------------------------------------------------+

--
c-town
------------------------------------------------------------------------
c-town's Profile: http://www.thecodecage.com/forumz/member.php?userid=695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125907