I was wondering if anyone could help me with creating a macro procedure to
do the following, which is a pain to do manually, and needs to be redone
every time anyone changes anything in my spreadsheet, which if often! I
think I've learned enough so that I can troubleshoot the code, should there
be any problem, if someone can supply it to me. By the way, the columns are
really, AU, BH, and AV but I thought it would be easier to ask for A, C, and
B below, and edit that myself. Please let me know if I have left anything
out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a time
(each row is one month later). If the entry is <= 10,000, then there is
nothing needed, so I want the cursor to simply move down to the next entry
in that column. If, in the Jth row, the entry is 10,000, then it needs to
do a test. It needs to look forward to the next row in this same column A
where there is another entry greater than 10,000 - let's call it the Kth row
(K <= 165, at some point, there will be no such 'future' value greater than
10,000, so then set K = 165). If the sum of entries in another column,
column C, from row 82 until (and including) this 'future' row K is zero,
then, once again, no action is required and we should just move down one row
(to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide the
cursor sideways over to the Jth row of column B and enter the value 10, then
repeatedly increment by 10 more, until the second condition above is met.
Occasionally, before this condition is met, another increment of 10 causes
the entry in row J of column C to exceed 999,999. If so, then I want to
back off to the prior increment of 10 and end the procedure for the Jth row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

<< Occasionally, before this condition is met, another increment of 10
causes
the entry in row J of column C to exceed 999,999. If so, then I want to
back off to the prior increment of 10 and end the procedure for the Jth row


Also, it needs to look at the Jth row in column BB (you can assume it's
called column D)
and if it is greater than 0.1, it should back off to the prior iteration
then too.

"Dean" wrote in message
...
I was wondering if anyone could help me with creating a macro procedure to
do the following, which is a pain to do manually, and needs to be redone
every time anyone changes anything in my spreadsheet, which if often! I
think I've learned enough so that I can troubleshoot the code, should there
be any problem, if someone can supply it to me. By the way, the columns
are really, AU, BH, and AV but I thought it would be easier to ask for A,
C, and B below, and edit that myself. Please let me know if I have left
anything out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time (each row is one month later). If the entry is <= 10,000, then there
is nothing needed, so I want the cursor to simply move down to the next
entry in that column. If, in the Jth row, the entry is 10,000, then it
needs to do a test. It needs to look forward to the next row in this same
column A where there is another entry greater than 10,000 - let's call it
the Kth row (K <= 165, at some point, there will be no such 'future' value
greater than 10,000, so then set K = 165). If the sum of entries in
another column, column C, from row 82 until (and including) this 'future'
row K is zero, then, once again, no action is required and we should just
move down one row (to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide the
cursor sideways over to the Jth row of column B and enter the value 10,
then repeatedly increment by 10 more, until the second condition above is
met. Occasionally, before this condition is met, another increment of 10
causes the entry in row J of column C to exceed 999,999. If so, then I
want to back off to the prior increment of 10 and end the procedure for
the Jth row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

I'm confused. It seems that your procedure could be more efficient but have
a look in the vba help index for FINDNEXT. There is a good example. You can
nest them.

--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
<< Occasionally, before this condition is met, another increment of 10
causes
the entry in row J of column C to exceed 999,999. If so, then I want to
back off to the prior increment of 10 and end the procedure for the Jth
row


Also, it needs to look at the Jth row in column BB (you can assume it's
called column D)
and if it is greater than 0.1, it should back off to the prior iteration
then too.

"Dean" wrote in message
...
I was wondering if anyone could help me with creating a macro procedure to
do the following, which is a pain to do manually, and needs to be redone
every time anyone changes anything in my spreadsheet, which if often! I
think I've learned enough so that I can troubleshoot the code, should
there be any problem, if someone can supply it to me. By the way, the
columns are really, AU, BH, and AV but I thought it would be easier to ask
for A, C, and B below, and edit that myself. Please let me know if I have
left anything out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time (each row is one month later). If the entry is <= 10,000, then
there is nothing needed, so I want the cursor to simply move down to the
next entry in that column. If, in the Jth row, the entry is 10,000,
then it needs to do a test. It needs to look forward to the next row in
this same column A where there is another entry greater than 10,000 -
let's call it the Kth row (K <= 165, at some point, there will be no such
'future' value greater than 10,000, so then set K = 165). If the sum of
entries in another column, column C, from row 82 until (and including)
this 'future' row K is zero, then, once again, no action is required and
we should just move down one row (to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the cursor sideways over to the Jth row of column B and enter the value
10, then repeatedly increment by 10 more, until the second condition
above is met. Occasionally, before this condition is met, another
increment of 10 causes the entry in row J of column C to exceed 999,999.
If so, then I want to back off to the prior increment of 10 and end the
procedure for the Jth row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

I looked up findnext but I just don't understand the syntax enough to work
through it.

I'm sorry that my explanation wasn't good enough for someone to try writing
the code. In the last e-mail, all I was saying is that I had realized that
there are two possible conditions, not just one, which would cause the
algorithm to go back to the prior increment of 10 and accept that (and then
move on to J+1). I hope that it was just that part that confused you.

If it is easier, I can create a new column in excel that, in each row J,
will compute how many rows there are between J and K, rather than making VBA
look down to find the next entry that is greater than 10,000. If it's
easier, just tell me to create such a column, and the macro can use its
result to decide how far to look ahead in the other column, rather than
trying to figure that out itself.

Please ask for any clarification.

Thanks, Don!
Dean

"Don Guillett" wrote in message
...
I'm confused. It seems that your procedure could be more efficient but
have a look in the vba help index for FINDNEXT. There is a good example.
You can nest them.

--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
<< Occasionally, before this condition is met, another increment of 10
causes
the entry in row J of column C to exceed 999,999. If so, then I want to
back off to the prior increment of 10 and end the procedure for the Jth
row


Also, it needs to look at the Jth row in column BB (you can assume it's
called column D)
and if it is greater than 0.1, it should back off to the prior iteration
then too.

"Dean" wrote in message
...
I was wondering if anyone could help me with creating a macro procedure
to do the following, which is a pain to do manually, and needs to be
redone every time anyone changes anything in my spreadsheet, which if
often! I think I've learned enough so that I can troubleshoot the code,
should there be any problem, if someone can supply it to me. By the way,
the columns are really, AU, BH, and AV but I thought it would be easier
to ask for A, C, and B below, and edit that myself. Please let me know
if I have left anything out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time (each row is one month later). If the entry is <= 10,000, then
there is nothing needed, so I want the cursor to simply move down to the
next entry in that column. If, in the Jth row, the entry is 10,000,
then it needs to do a test. It needs to look forward to the next row in
this same column A where there is another entry greater than 10,000 -
let's call it the Kth row (K <= 165, at some point, there will be no
such 'future' value greater than 10,000, so then set K = 165). If the
sum of entries in another column, column C, from row 82 until (and
including) this 'future' row K is zero, then, once again, no action is
required and we should just move down one row (to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the cursor sideways over to the Jth row of column B and enter the value
10, then repeatedly increment by 10 more, until the second condition
above is met. Occasionally, before this condition is met, another
increment of 10 causes the entry in row J of column C to exceed 999,999.
If so, then I want to back off to the prior increment of 10 and end the
procedure for the Jth row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

BTW to have it fire each time someone makes a change, put into a
worksheet_change event within the worksheet module.
--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
I was wondering if anyone could help me with creating a macro procedure to
do the following, which is a pain to do manually, and needs to be redone
every time anyone changes anything in my spreadsheet, which if often! I
think I've learned enough so that I can troubleshoot the code, should there
be any problem, if someone can supply it to me. By the way, the columns
are really, AU, BH, and AV but I thought it would be easier to ask for A,
C, and B below, and edit that myself. Please let me know if I have left
anything out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time (each row is one month later). If the entry is <= 10,000, then there
is nothing needed, so I want the cursor to simply move down to the next
entry in that column. If, in the Jth row, the entry is 10,000, then it
needs to do a test. It needs to look forward to the next row in this same
column A where there is another entry greater than 10,000 - let's call it
the Kth row (K <= 165, at some point, there will be no such 'future' value
greater than 10,000, so then set K = 165). If the sum of entries in
another column, column C, from row 82 until (and including) this 'future'
row K is zero, then, once again, no action is required and we should just
move down one row (to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide the
cursor sideways over to the Jth row of column B and enter the value 10,
then repeatedly increment by 10 more, until the second condition above is
met. Occasionally, before this condition is met, another increment of 10
causes the entry in row J of column C to exceed 999,999. If so, then I
want to back off to the prior increment of 10 and end the procedure for
the Jth row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

If you like you can send me a workbook with exact details of what you want
and copies of these messages.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...

BTW to have it fire each time someone makes a change, put into a
worksheet_change event within the worksheet module.
--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
I was wondering if anyone could help me with creating a macro procedure to
do the following, which is a pain to do manually, and needs to be redone
every time anyone changes anything in my spreadsheet, which if often! I
think I've learned enough so that I can troubleshoot the code, should
there be any problem, if someone can supply it to me. By the way, the
columns are really, AU, BH, and AV but I thought it would be easier to ask
for A, C, and B below, and edit that myself. Please let me know if I have
left anything out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time (each row is one month later). If the entry is <= 10,000, then
there is nothing needed, so I want the cursor to simply move down to the
next entry in that column. If, in the Jth row, the entry is 10,000,
then it needs to do a test. It needs to look forward to the next row in
this same column A where there is another entry greater than 10,000 -
let's call it the Kth row (K <= 165, at some point, there will be no such
'future' value greater than 10,000, so then set K = 165). If the sum of
entries in another column, column C, from row 82 until (and including)
this 'future' row K is zero, then, once again, no action is required and
we should just move down one row (to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the cursor sideways over to the Jth row of column B and enter the value
10, then repeatedly increment by 10 more, until the second condition
above is met. Occasionally, before this condition is met, another
increment of 10 causes the entry in row J of column C to exceed 999,999.
If so, then I want to back off to the prior increment of 10 and end the
procedure for the Jth row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

Much appreciated but, if I have to do that, it seems like I'm asking for too
much. I will e-mail you about what you might charge. I would need to clean
out some stuff because it is proprietary and I would get in trouble
otherwise and I'm off on another job for about 10 days, so it won't be
before then probably.

Actually, I have hired one of you guys for one job but he took new
employment just after and is just too swamped the last two times I've asked
him for more help. I won't mention his name because he may not want me to,
but if he reads this and wants to tell anyone I'm safe, I hope he will!

Thanks again
Dean

"Don Guillett" wrote in message
...
If you like you can send me a workbook with exact details of what you want
and copies of these messages.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...

BTW to have it fire each time someone makes a change, put into a
worksheet_change event within the worksheet module.
--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
I was wondering if anyone could help me with creating a macro procedure
to do the following, which is a pain to do manually, and needs to be
redone every time anyone changes anything in my spreadsheet, which if
often! I think I've learned enough so that I can troubleshoot the code,
should there be any problem, if someone can supply it to me. By the way,
the columns are really, AU, BH, and AV but I thought it would be easier
to ask for A, C, and B below, and edit that myself. Please let me know
if I have left anything out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time (each row is one month later). If the entry is <= 10,000, then
there is nothing needed, so I want the cursor to simply move down to the
next entry in that column. If, in the Jth row, the entry is 10,000,
then it needs to do a test. It needs to look forward to the next row in
this same column A where there is another entry greater than 10,000 -
let's call it the Kth row (K <= 165, at some point, there will be no
such 'future' value greater than 10,000, so then set K = 165). If the
sum of entries in another column, column C, from row 82 until (and
including) this 'future' row K is zero, then, once again, no action is
required and we should just move down one row (to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the cursor sideways over to the Jth row of column B and enter the value
10, then repeatedly increment by 10 more, until the second condition
above is met. Occasionally, before this condition is met, another
increment of 10 causes the entry in row J of column C to exceed 999,999.
If so, then I want to back off to the prior increment of 10 and end the
procedure for the Jth row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

Send me a private email if you like
--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
Much appreciated but, if I have to do that, it seems like I'm asking for
too much. I will e-mail you about what you might charge. I would need to
clean out some stuff because it is proprietary and I would get in trouble
otherwise and I'm off on another job for about 10 days, so it won't be
before then probably.

Actually, I have hired one of you guys for one job but he took new
employment just after and is just too swamped the last two times I've
asked him for more help. I won't mention his name because he may not want
me to, but if he reads this and wants to tell anyone I'm safe, I hope he
will!

Thanks again
Dean

"Don Guillett" wrote in message
...
If you like you can send me a workbook with exact details of what you
want and copies of these messages.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...

BTW to have it fire each time someone makes a change, put into a
worksheet_change event within the worksheet module.
--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
I was wondering if anyone could help me with creating a macro procedure
to do the following, which is a pain to do manually, and needs to be
redone every time anyone changes anything in my spreadsheet, which if
often! I think I've learned enough so that I can troubleshoot the code,
should there be any problem, if someone can supply it to me. By the
way, the columns are really, AU, BH, and AV but I thought it would be
easier to ask for A, C, and B below, and edit that myself. Please let
me know if I have left anything out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time (each row is one month later). If the entry is <= 10,000, then
there is nothing needed, so I want the cursor to simply move down to
the next entry in that column. If, in the Jth row, the entry is
10,000, then it needs to do a test. It needs to look forward to the
next row in this same column A where there is another entry greater
than 10,000 - let's call it the Kth row (K <= 165, at some point, there
will be no such 'future' value greater than 10,000, so then set K =
165). If the sum of entries in another column, column C, from row 82
until (and including) this 'future' row K is zero, then, once again, no
action is required and we should just move down one row (to J+1) in
column A.



However, if the above test fails to produce zero, then I want to slide
the cursor sideways over to the Jth row of column B and enter the value
10, then repeatedly increment by 10 more, until the second condition
above is met. Occasionally, before this condition is met, another
increment of 10 causes the entry in row J of column C to exceed
999,999. If so, then I want to back off to the prior increment of 10
and end the procedure for the Jth row.



Then, go back to column A and continue with the J+1st row, ending at
row 164.



Thanks so much!

Dean

Thanks, Don. Let me see what progresses, but I think drhalter has me
covered on this one.

D

"Don Guillett" wrote in message
...

Send me a private email if you like
--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
Much appreciated but, if I have to do that, it seems like I'm asking for
too much. I will e-mail you about what you might charge. I would need
to clean out some stuff because it is proprietary and I would get in
trouble otherwise and I'm off on another job for about 10 days, so it
won't be before then probably.

Actually, I have hired one of you guys for one job but he took new
employment just after and is just too swamped the last two times I've
asked him for more help. I won't mention his name because he may not
want me to, but if he reads this and wants to tell anyone I'm safe, I
hope he will!

Thanks again
Dean

"Don Guillett" wrote in message
...
If you like you can send me a workbook with exact details of what you
want and copies of these messages.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...

BTW to have it fire each time someone makes a change, put into a
worksheet_change event within the worksheet module.
--
Don Guillett
SalesAid Software

"Dean" wrote in message
...
I was wondering if anyone could help me with creating a macro procedure
to do the following, which is a pain to do manually, and needs to be
redone every time anyone changes anything in my spreadsheet, which if
often! I think I've learned enough so that I can troubleshoot the
code, should there be any problem, if someone can supply it to me. By
the way, the columns are really, AU, BH, and AV but I thought it would
be easier to ask for A, C, and B below, and edit that myself. Please
let me know if I have left anything out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time (each row is one month later). If the entry is <= 10,000, then
there is nothing needed, so I want the cursor to simply move down to
the next entry in that column. If, in the Jth row, the entry is
10,000, then it needs to do a test. It needs to look forward to the
next row in this same column A where there is another entry greater
than 10,000 - let's call it the Kth row (K <= 165, at some point,
there will be no such 'future' value greater than 10,000, so then set
K = 165). If the sum of entries in another column, column C, from
row 82 until (and including) this 'future' row K is zero, then, once
again, no action is required and we should just move down one row (to
J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the cursor sideways over to the Jth row of column B and enter the
value 10, then repeatedly increment by 10 more, until the second
condition above is met. Occasionally, before this condition is met,
another increment of 10 causes the entry in row J of column C to
exceed 999,999. If so, then I want to back off to the prior increment
of 10 and end the procedure for the Jth row.



Then, go back to column A and continue with the J+1st row, ending at
row 164.



Thanks so much!

Dean

Sounds like a nice little problem. I thought I'd work on it a bit, but it
might be easier if you post the code you've already come up with.

drhalter

"Dean" wrote:

I was wondering if anyone could help me with creating a macro procedure to
do the following, which is a pain to do manually, and needs to be redone
every time anyone changes anything in my spreadsheet, which if often! I
think I've learned enough so that I can troubleshoot the code, should there
be any problem, if someone can supply it to me. By the way, the columns are
really, AU, BH, and AV but I thought it would be easier to ask for A, C, and
B below, and edit that myself. Please let me know if I have left anything
out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a time
(each row is one month later). If the entry is <= 10,000, then there is
nothing needed, so I want the cursor to simply move down to the next entry
in that column. If, in the Jth row, the entry is 10,000, then it needs to
do a test. It needs to look forward to the next row in this same column A
where there is another entry greater than 10,000 - let's call it the Kth row
(K <= 165, at some point, there will be no such 'future' value greater than
10,000, so then set K = 165). If the sum of entries in another column,
column C, from row 82 until (and including) this 'future' row K is zero,
then, once again, no action is required and we should just move down one row
(to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide the
cursor sideways over to the Jth row of column B and enter the value 10, then
repeatedly increment by 10 more, until the second condition above is met.
Occasionally, before this condition is met, another increment of 10 causes
the entry in row J of column C to exceed 999,999. If so, then I want to
back off to the prior increment of 10 and end the procedure for the Jth row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

I have not made any progress on a macro. I can troubleshoot and edit some
macros (and, of course, record some), but just don't do it frequently enough
to have gotten good enough to attempt to write it. I'm an EXCEL jockey from
the olden days, but never really learned VBE.

If you'd like to take a shot at it, from what I've posted, or ask questions
first, that would be wonderful. I might be able to take it to the finish
line from there. Mostly, it's the syntax where I get thrown.

Thanks!
Dean

"drhalter" wrote in message
...
Sounds like a nice little problem. I thought I'd work on it a bit, but it
might be easier if you post the code you've already come up with.

drhalter

"Dean" wrote:

I was wondering if anyone could help me with creating a macro procedure
to
do the following, which is a pain to do manually, and needs to be redone
every time anyone changes anything in my spreadsheet, which if often! I
think I've learned enough so that I can troubleshoot the code, should
there
be any problem, if someone can supply it to me. By the way, the columns
are
really, AU, BH, and AV but I thought it would be easier to ask for A, C,
and
B below, and edit that myself. Please let me know if I have left
anything
out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time
(each row is one month later). If the entry is <= 10,000, then there is
nothing needed, so I want the cursor to simply move down to the next
entry
in that column. If, in the Jth row, the entry is 10,000, then it needs
to
do a test. It needs to look forward to the next row in this same column
A
where there is another entry greater than 10,000 - let's call it the Kth
row
(K <= 165, at some point, there will be no such 'future' value greater
than
10,000, so then set K = 165). If the sum of entries in another column,
column C, from row 82 until (and including) this 'future' row K is zero,
then, once again, no action is required and we should just move down one
row
(to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the
cursor sideways over to the Jth row of column B and enter the value 10,
then
repeatedly increment by 10 more, until the second condition above is met.
Occasionally, before this condition is met, another increment of 10
causes
the entry in row J of column C to exceed 999,999. If so, then I want to
back off to the prior increment of 10 and end the procedure for the Jth
row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

Okay, here we go. Try this one on for size. I'm not sure that I really got
the tests and conditions right, but I tried to follow your instructions as
precisely as I could.

This code contains a Do...Loop section of code in it. From your
instruction, I understand this code in your worksheet will eventually produce
a sum (called myvalue in the code) that is equal to zero. If, however,
myvalue never reaches 0, the code will continue to run through that loop
almost indefinitely (until the computer runs out of memory for the number it
is supposed to store in myvalue)....

All that is to say, before you start troubleshooting the code, put a
breakpoint within the Do...Loop section. That way, if the code isn't working
right, you have a way out. When you do that, the code will stop at the
breakpoint every time, and to continue you will have to push the "play"
button in VB. You can set a breakpoint by putting the cursor on the line
you want and press F9, or by choosing Toggle breakpoint in the Debug menu, or
by clicking in the left margin of the code. You should see a red highlight
across the line of code and a red dot to the left.

Also, while troubleshooting, I would put the code in a module. Then, when
you are satisfied it is working right, then move it to the Worksheet_Change()
module.

Here it is:


Sub myloop()
Dim j As Integer
For j = 82 To 164 'start looking through cells A82 to A164
myvalue = 0
If Cells(j, 1) <= 10000 Then
'do nothing
Else

For k = j + 1 To 165
If Cells(k, 1).Value 10000 Then 'start looking and testing
cells in the j+1 'row to row 165 in column A
For l = 82 To k ' loop to sum cells in column C from row 82
to row k
myvalue = myvalue + Cells(l, 3).Value
Next l
If myvalue = 0 Then
Exit For ' go to the outer loop, check cell j+1
Else 'check conditions if sum wasn't zero.
myincr = Cells(j, 2).Value
Do
myvalue = Empty
myincr = myincr + 10
Cells(j, 2).Value = myincr

If Cells(j, 3) 999999 Then
Cells(j, 2).Value = Cells(j, 2).Value - 10
Exit Do
End If

For l = 82 To k
myvalue = myvalue + Cells(l, 3).Value
Next l

If myvalue = 0 Then
Exit Do
End If
Loop
End If
Exit For 'once all the above conditions are met, move on to
the j+1 row
End If
Next k
End If
Next j

End Sub

Good Luck,
drhalter

"Dean" wrote:

I have not made any progress on a macro. I can troubleshoot and edit some
macros (and, of course, record some), but just don't do it frequently enough
to have gotten good enough to attempt to write it. I'm an EXCEL jockey from
the olden days, but never really learned VBE.

If you'd like to take a shot at it, from what I've posted, or ask questions
first, that would be wonderful. I might be able to take it to the finish
line from there. Mostly, it's the syntax where I get thrown.

Thanks!
Dean

"drhalter" wrote in message
...
Sounds like a nice little problem. I thought I'd work on it a bit, but it
might be easier if you post the code you've already come up with.

drhalter

"Dean" wrote:

I was wondering if anyone could help me with creating a macro procedure
to
do the following, which is a pain to do manually, and needs to be redone
every time anyone changes anything in my spreadsheet, which if often! I
think I've learned enough so that I can troubleshoot the code, should
there
be any problem, if someone can supply it to me. By the way, the columns
are
really, AU, BH, and AV but I thought it would be easier to ask for A, C,
and
B below, and edit that myself. Please let me know if I have left
anything
out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time
(each row is one month later). If the entry is <= 10,000, then there is
nothing needed, so I want the cursor to simply move down to the next
entry
in that column. If, in the Jth row, the entry is 10,000, then it needs
to
do a test. It needs to look forward to the next row in this same column
A
where there is another entry greater than 10,000 - let's call it the Kth
row
(K <= 165, at some point, there will be no such 'future' value greater
than
10,000, so then set K = 165). If the sum of entries in another column,
column C, from row 82 until (and including) this 'future' row K is zero,
then, once again, no action is required and we should just move down one
row
(to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the
cursor sideways over to the Jth row of column B and enter the value 10,
then
repeatedly increment by 10 more, until the second condition above is met.
Occasionally, before this condition is met, another increment of 10
causes
the entry in row J of column C to exceed 999,999. If so, then I want to
back off to the prior increment of 10 and end the procedure for the Jth
row.



Then, go back to column A and continue with the J+1st row, ending at row
164.



Thanks so much!

Dean

Looks great! I'm not to swift with breakpoints but I know the value would
never be great than 5,000,000, so can you edit it to terminate if it reaches
5,000,000, and repost?

Also, I don't know if you saw my 2nd post that said it should back off by
one increment if either the original condition (the 99,999 one) I had
specified happened OR another condition happened. The other condition would
be that it needs to look at the Jth row in another column D and if it is
greater than 0.1, it should back off to the prior iteration then too.

Thanks!
Dean



"drhalter" wrote in message
...
Okay, here we go. Try this one on for size. I'm not sure that I really
got
the tests and conditions right, but I tried to follow your instructions as
precisely as I could.

This code contains a Do...Loop section of code in it. From your
instruction, I understand this code in your worksheet will eventually
produce
a sum (called myvalue in the code) that is equal to zero. If, however,
myvalue never reaches 0, the code will continue to run through that loop
almost indefinitely (until the computer runs out of memory for the number
it
is supposed to store in myvalue)....

All that is to say, before you start troubleshooting the code, put a
breakpoint within the Do...Loop section. That way, if the code isn't
working
right, you have a way out. When you do that, the code will stop at the
breakpoint every time, and to continue you will have to push the "play"
button in VB. You can set a breakpoint by putting the cursor on the
line
you want and press F9, or by choosing Toggle breakpoint in the Debug menu,
or
by clicking in the left margin of the code. You should see a red
highlight
across the line of code and a red dot to the left.

Also, while troubleshooting, I would put the code in a module. Then, when
you are satisfied it is working right, then move it to the
Worksheet_Change()
module.

Here it is:


Sub myloop()
Dim j As Integer
For j = 82 To 164 'start looking through cells A82 to A164
myvalue = 0
If Cells(j, 1) <= 10000 Then
'do nothing
Else

For k = j + 1 To 165
If Cells(k, 1).Value 10000 Then 'start looking and testing
cells in the j+1 'row to row 165 in column A
For l = 82 To k ' loop to sum cells in column C from row
82
to row k
myvalue = myvalue + Cells(l, 3).Value
Next l
If myvalue = 0 Then
Exit For ' go to the outer loop, check cell j+1
Else 'check conditions if sum wasn't zero.
myincr = Cells(j, 2).Value
Do
myvalue = Empty
myincr = myincr + 10
Cells(j, 2).Value = myincr

If Cells(j, 3) 999999 Then
Cells(j, 2).Value = Cells(j, 2).Value - 10
Exit Do
End If

For l = 82 To k
myvalue = myvalue + Cells(l, 3).Value
Next l

If myvalue = 0 Then
Exit Do
End If
Loop
End If
Exit For 'once all the above conditions are met, move on to
the j+1 row
End If
Next k
End If
Next j

End Sub

Good Luck,
drhalter

"Dean" wrote:

I have not made any progress on a macro. I can troubleshoot and edit
some
macros (and, of course, record some), but just don't do it frequently
enough
to have gotten good enough to attempt to write it. I'm an EXCEL jockey
from
the olden days, but never really learned VBE.

If you'd like to take a shot at it, from what I've posted, or ask
questions
first, that would be wonderful. I might be able to take it to the finish
line from there. Mostly, it's the syntax where I get thrown.

Thanks!
Dean

"drhalter" wrote in message
...
Sounds like a nice little problem. I thought I'd work on it a bit, but
it
might be easier if you post the code you've already come up with.

drhalter

"Dean" wrote:

I was wondering if anyone could help me with creating a macro
procedure
to
do the following, which is a pain to do manually, and needs to be
redone
every time anyone changes anything in my spreadsheet, which if often!
I
think I've learned enough so that I can troubleshoot the code, should
there
be any problem, if someone can supply it to me. By the way, the
columns
are
really, AU, BH, and AV but I thought it would be easier to ask for A,
C,
and
B below, and edit that myself. Please let me know if I have left
anything
out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time
(each row is one month later). If the entry is <= 10,000, then there
is
nothing needed, so I want the cursor to simply move down to the next
entry
in that column. If, in the Jth row, the entry is 10,000, then it
needs
to
do a test. It needs to look forward to the next row in this same
column
A
where there is another entry greater than 10,000 - let's call it the
Kth
row
(K <= 165, at some point, there will be no such 'future' value greater
than
10,000, so then set K = 165). If the sum of entries in another
column,
column C, from row 82 until (and including) this 'future' row K is
zero,
then, once again, no action is required and we should just move down
one
row
(to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the
cursor sideways over to the Jth row of column B and enter the value
10,
then
repeatedly increment by 10 more, until the second condition above is
met.
Occasionally, before this condition is met, another increment of 10
causes
the entry in row J of column C to exceed 999,999. If so, then I want
to
back off to the prior increment of 10 and end the procedure for the
Jth
row.



Then, go back to column A and continue with the J+1st row, ending at
row
164.



Thanks so much!

Dean

To stop the Do...Loop section of code, there is the code:
If myvalue = 0 then
exit do
End If

Edit this to:
If myvalue = 0 then
exit do
ElseIf myvalue 5000000
Msgbox("Oops, column C added to a value greater than 5,000,000!")
exit do
End If

As to the second condition:
Note where I have the test for 999999. If it doesn't matter which order
the code checks, simply put a similar set of code below that test line. From
what I have, you should be able to figure out what code to add.

Note also that my code tests for 999,999 as per your original post. In
your reply, you have 99,999. Better make sure that test is testing for the
right number.

drhalter


"Dean" wrote:

Looks great! I'm not to swift with breakpoints but I know the value would
never be great than 5,000,000, so can you edit it to terminate if it reaches
5,000,000, and repost?

Also, I don't know if you saw my 2nd post that said it should back off by
one increment if either the original condition (the 99,999 one) I had
specified happened OR another condition happened. The other condition would
be that it needs to look at the Jth row in another column D and if it is
greater than 0.1, it should back off to the prior iteration then too.

Thanks!
Dean



"drhalter" wrote in message
...
Okay, here we go. Try this one on for size. I'm not sure that I really
got
the tests and conditions right, but I tried to follow your instructions as
precisely as I could.

This code contains a Do...Loop section of code in it. From your
instruction, I understand this code in your worksheet will eventually
produce
a sum (called myvalue in the code) that is equal to zero. If, however,
myvalue never reaches 0, the code will continue to run through that loop
almost indefinitely (until the computer runs out of memory for the number
it
is supposed to store in myvalue)....

All that is to say, before you start troubleshooting the code, put a
breakpoint within the Do...Loop section. That way, if the code isn't
working
right, you have a way out. When you do that, the code will stop at the
breakpoint every time, and to continue you will have to push the "play"
button in VB. You can set a breakpoint by putting the cursor on the
line
you want and press F9, or by choosing Toggle breakpoint in the Debug menu,
or
by clicking in the left margin of the code. You should see a red
highlight
across the line of code and a red dot to the left.

Also, while troubleshooting, I would put the code in a module. Then, when
you are satisfied it is working right, then move it to the
Worksheet_Change()
module.

Here it is:


Sub myloop()
Dim j As Integer
For j = 82 To 164 'start looking through cells A82 to A164
myvalue = 0
If Cells(j, 1) <= 10000 Then
'do nothing
Else

For k = j + 1 To 165
If Cells(k, 1).Value 10000 Then 'start looking and testing
cells in the j+1 'row to row 165 in column A
For l = 82 To k ' loop to sum cells in column C from row
82
to row k
myvalue = myvalue + Cells(l, 3).Value
Next l
If myvalue = 0 Then
Exit For ' go to the outer loop, check cell j+1
Else 'check conditions if sum wasn't zero.
myincr = Cells(j, 2).Value
Do
myvalue = Empty
myincr = myincr + 10
Cells(j, 2).Value = myincr

If Cells(j, 3) 999999 Then
Cells(j, 2).Value = Cells(j, 2).Value - 10
Exit Do
End If

For l = 82 To k
myvalue = myvalue + Cells(l, 3).Value
Next l

If myvalue = 0 Then
Exit Do
End If
Loop
End If
Exit For 'once all the above conditions are met, move on to
the j+1 row
End If
Next k
End If
Next j

End Sub

Good Luck,
drhalter

"Dean" wrote:

I have not made any progress on a macro. I can troubleshoot and edit
some
macros (and, of course, record some), but just don't do it frequently
enough
to have gotten good enough to attempt to write it. I'm an EXCEL jockey
from
the olden days, but never really learned VBE.

If you'd like to take a shot at it, from what I've posted, or ask
questions
first, that would be wonderful. I might be able to take it to the finish
line from there. Mostly, it's the syntax where I get thrown.

Thanks!
Dean

"drhalter" wrote in message
...
Sounds like a nice little problem. I thought I'd work on it a bit, but
it
might be easier if you post the code you've already come up with.

drhalter

"Dean" wrote:

I was wondering if anyone could help me with creating a macro
procedure
to
do the following, which is a pain to do manually, and needs to be
redone
every time anyone changes anything in my spreadsheet, which if often!
I
think I've learned enough so that I can troubleshoot the code, should
there
be any problem, if someone can supply it to me. By the way, the
columns
are
really, AU, BH, and AV but I thought it would be easier to ask for A,
C,
and
B below, and edit that myself. Please let me know if I have left
anything
out.



From rows 82 to 164, inclusive, I want to check Column A, one row at a
time
(each row is one month later). If the entry is <= 10,000, then there
is
nothing needed, so I want the cursor to simply move down to the next
entry
in that column. If, in the Jth row, the entry is 10,000, then it
needs
to
do a test. It needs to look forward to the next row in this same
column
A
where there is another entry greater than 10,000 - let's call it the
Kth
row
(K <= 165, at some point, there will be no such 'future' value greater
than
10,000, so then set K = 165). If the sum of entries in another
column,
column C, from row 82 until (and including) this 'future' row K is
zero,
then, once again, no action is required and we should just move down
one
row
(to J+1) in column A.



However, if the above test fails to produce zero, then I want to slide
the
cursor sideways over to the Jth row of column B and enter the value
10,
then
repeatedly increment by 10 more, until the second condition above is
met.
Occasionally, before this condition is met, another increment of 10
causes
the entry in row J of column C to exceed 999,999. If so, then I want
to
back off to the prior increment of 10 and end the procedure for the
Jth
row.



Then, go back to column A and continue with the J+1st row, ending at
row
164.



Thanks so much!

Dean