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