I'm trying to create a macro to mark flags in a worksheet to be used in
another program to analyze data.
The sheet looks something like this:


ID Flag Date
G4 0 02/25/04
H6 1 09/07/03


Actually, there are additional fields between these with text and
numbers not relevant to the macro.
I want the macro to search first in the Flag field until it finds a
"1". I want it to store the ID (iBaseSID) and date (iBaseDate), and
calculate the date 90 days earlier (iEarlyDate). I then want it to
back up one line and compare ID's to see if they are the same. If they
are, I want it to check and see if the CurDate (the date on the current
line) is greater than the EarlyDate (BaseDate-90). If both conditions
are met, I want it to change the current Flag to 1 (It won't be 1
because it was tested before and didn't = 1, other wise the macro would
be start testing the other variables on that line).
I want it to contunue moving up one line at at time, changing the flag
until one of the conditions is not met, then go to the line below the
Base line and repeat the process going down, except that the date it
will be testing for is iLateDate (iBaseDate + 90) and it will test for
less than or equal to. (Actually, there is a third test that will be
run, but I figured that if I got the code to get me this far, I can
figure out how to add one more test loop.)
The loops end when the cell is blank, or when the range ends (each
column is a named range), whichever is easiest.
Thanks for the help!

Dave

It is still a little unclear how you want this to work. Do you want to find
the first instance of "1" and test every other row (up and down) based on
this row or do you want to test every instance of "1" (checking up and down)
to the end of your data.

If you want to check the first instance of 1 then this code will get you
started. The fuction is some simple error handling in case there are no cells
with the flag 1.

Sub TestLoop()

Dim testRow As Long
Dim iBaseID As String
Dim iBaseDate, iEarlyDate, iLateDate As Date
Dim testCond As Boolean
Dim searchFlag As Integer

Range("B2").Select 'first flag
searchFlag = 1
Range(Selection, Selection.End(xlDown)).Select
If Not findFlag(searchFlag) Then
MsgBox "No flag " & searchFlag & " found"
Else
testRow = ActiveCell.Row
iBaseID = ActiveCell.Offset(0, -1).Value
iBaseDate = ActiveCell.Offset(0, 1).Value
iEarlyDate = iBaseDate - 90
iLateDate = iBaseDate + 90

testCond = True
Do While testCond = True
ActiveCell.Offset(-1, 0).Select
If ActiveCell.Offset(0, -1).Value = iBaseID _
And ActiveCell.Offset(0, 1).Value iEarlyDate Then
ActiveCell.Value = 1 ' changes flag
Else
testCond = False
End If
Loop
Cells(testRow + 1, 2).Select ' moves to row after testcell
' add another loop here to continue moving down

End If

End Sub

Function findFlag(searchFlag) As Boolean

On Error GoTo NotFound
Selection.Find(What:=searchFlag, After:=ActiveCell _
, LookIn:=xlValues, LookAt:=xlWhole _
, SearchOrder:=xlByColumns _
, SearchDirection:=xlNext).Activate
findFlag = True
Exit Function

NotFound:
findFlag = False

End Function


Regards
Rowan

"davegb" wrote:

I'm trying to create a macro to mark flags in a worksheet to be used in
another program to analyze data.
The sheet looks something like this:


ID Flag Date
G4 0 02/25/04
H6 1 09/07/03


Actually, there are additional fields between these with text and
numbers not relevant to the macro.
I want the macro to search first in the Flag field until it finds a
"1". I want it to store the ID (iBaseSID) and date (iBaseDate), and
calculate the date 90 days earlier (iEarlyDate). I then want it to
back up one line and compare ID's to see if they are the same. If they
are, I want it to check and see if the CurDate (the date on the current
line) is greater than the EarlyDate (BaseDate-90). If both conditions
are met, I want it to change the current Flag to 1 (It won't be 1
because it was tested before and didn't = 1, other wise the macro would
be start testing the other variables on that line).
I want it to contunue moving up one line at at time, changing the flag
until one of the conditions is not met, then go to the line below the
Base line and repeat the process going down, except that the date it
will be testing for is iLateDate (iBaseDate + 90) and it will test for
less than or equal to. (Actually, there is a third test that will be
run, but I figured that if I got the code to get me this far, I can
figure out how to add one more test loop.)
The loops end when the cell is blank, or when the range ends (each
column is a named range), whichever is easiest.
Thanks for the help!

Rowan,
Thanks for the help! This is a great start.

"It is still a little unclear how you want this to work. Do you want to
find
the first instance of "1" and test every other row (up and down) based
on
this row or do you want to test every instance of "1" (checking up and
down)
to the end of your data."

It's the second option. The triggering events have already been flagged
with a 1. I need to flag with a 1 all events that involving that
particular client (the ID) that occurred within 90 days of the
triggering event. After they've been flagged, the macro needs to search
for the next 1, which will be the next client, and repeat the process
until the data runs out.
There are no blank cells to throw it off that way. The only blanks are
after the data.
I hope this is clearer now.

I have a few questions about the code. It's far more involved than I
had anticipated. What is the purpose of having the "Function"? I would
think that this kind of test could be done in "normal" coding.
Obviously, you know something about this that makes it faster or
simpler or in some other way better to use the function. I've heard of
VBA functions recently, but don't fully understand the concept. The
function seems to me similar to a subroutine in other languages I've
programmed in during a previous life. I want to make it clear that I'm
not second guessing you, only trying to learn and understand the
reasons behind what you've obviously done well so I can learn to be a
good VBA programmer.

Why is TestRow dimmed as long? Isn't it an integer? I've seen this in
other code I've looked at in this forum and wondered what the logic is
behind it.

" ' add another loop here to continue moving down"
I think you mean just to put the command "Loop" here to keep searching
for additional 1's. I'll probably have tried it before I get your reply
to this message.

For some reason, my gut tells me that this is sophisticated programming
you've done here. I can't even tell you why I think so, but I've
learned over the years that I have excellent instincts at recognizing
really good work even when I have no real quantitative knowledge of
what good work should look like. Those instincts tell me you're an
accomplished programmer, and I appreciate your taking the time to help
a beginner in a bind.

Dave B.

Rowan,
I've worked on this macro, changed some variable names and offsets to
match the actual database I'm working with. I've added 2 loops so it
can search for and change additional flags.
It now looks like this:

Sub MarkAdoptRows()

Dim sBaseSID As String
Dim sCurSID As String
Dim iCurRow As Integer
Dim lBaseRow As Long
Dim dBaseDate, dEarlyDate, dLateDate As Date
Dim testCond As Boolean
Dim searchFlag As Integer

Range("c2").Select 'first flag
searchFlag = 1
Range(Selection, Selection.End(xlDown)).Select
If Not findFlag(searchFlag) Then
MsgBox "No flag " & searchFlag & " found"
Else
lBaseRow = ActiveCell.Row
sBaseSID = ActiveCell.Offset(0, -2).Value
dBaseDate = ActiveCell.Offset(0, 2).Value
dEarlyDate = dBaseDate - 90
dLateDate = dBaseDate + 90
testCond = True
Do While testCond = True
Do While testCond = True
ActiveCell.Offset(-1, 0).Select
If ActiveCell.Offset(0, -2).Value = sBaseSID _
And ActiveCell.Offset(0, 2).Value dEarlyDate Then
ActiveCell.Value = 1 ' changes flag
Else
testCond = False
End If
Loop
Cells(lBaseRow + 1).Select ' moves to row after testcell
Do While testCond = True
ActiveCell.Offset(0, 1).Select
If ActiveCell.Offset(0, -2).Value = sBaseSID _
And ActiveCell.Offset(0, 2).Value < dLateDate Then
ActiveCell.Value = 1 ' changes flag
Else
testCond = False
End If
Loop ' add another loop here to continue moving down
Loop
End If
End Sub
Function findFlag(searchFlag) As Boolean
On Error GoTo NotFound
Selection.Find(What:=searchFlag, After:=ActiveCell _
, LookIn:=xlValues, LookAt:=xlWhole _
, SearchOrder:=xlByColumns _
, SearchDirection:=xlNext).Activate
findFlag = True
Exit Function
NotFound:
findFlag = False
End Function

The problem is this. It runs, but when after it's run, it tests for no
more flags, and when there are not more, it locks up with the message
box and I have to Ctl-Alt-Del and shut down XL in the Task Manager!
Instead of testing for no flags, as it currently does, I want it to
test for a blank cell, which I've added in. Problem is, I don't know
how to re-write the top part,

Range("c2").Select 'first flag
searchFlag = 1
Range(Selection, Selection.End(xlDown)).Select
If Not findFlag(searchFlag) Then
MsgBox "No flag " & searchFlag & " found"

to just run the search, not the test. The message box is hanging up the
macro for some reason, I think probably putting it in an endless loop
when combined with the loops I added. So the program works until it
gets to the end. How can I fix this?
Thanks again!

Dave

Firstly to answer your questions: the reason I have used Long as the
variable type for testRow is that the maximum positive value for an integer
is 32,767. As you know, newer versions of Excel have 65,536 rows so you could
have enough data in your sheet so that an integer variable would go out of
range causing an error.

I have used the function to trap the known erorr where a flag of 1 is not
found. You could include this in the main routine but normally you would have
seperate error handling code in there to trap other errors that may occur -
some of which you may not have anticipated. It makes it easier to keep track
of this specific error by handling it seperately in the function and also
this allows you to treat this error differently from how you may treat other
errors.

Now to the code. I haven't looked at the changes you have made in detail as
the code I gave you was to deal with option 1 that I mentioned. As we
actually need to do option 2 the result will be quite different. We now want
to find every occurence of "1" so it will be easier to use a loop through
every row of data rather than using the Excel "Find" . This will also take
away the need to have a function to trap the error if there are no 1 flags.

Before I do this though I just want to clear up one more thing. In your data
are all the rows for an ID together. This is the impression that I get. If
this is the case then the code we need should:

Start at first row.
Find first flag "1" and get corresponding ID.
Check every occurence of this ID above the flag. Where date on these lines
is greater than base date - 90 change flag to 1.
Check every occurence of the ID below the flag. Where date on these lines is
<= base date + 90 change flag to 1.
Then move to next ID, search for flag 1 and repeat process.
Repeat until first blank flag found.

Please confirm that these sound like the right steps and then I can look at
the code for you.

Regards
Rowan

"davegb" wrote:

I'm trying to create a macro to mark flags in a worksheet to be used in
another program to analyze data.
The sheet looks something like this:


ID Flag Date
G4 0 02/25/04
H6 1 09/07/03


Actually, there are additional fields between these with text and
numbers not relevant to the macro.
I want the macro to search first in the Flag field until it finds a
"1". I want it to store the ID (iBaseSID) and date (iBaseDate), and
calculate the date 90 days earlier (iEarlyDate). I then want it to
back up one line and compare ID's to see if they are the same. If they
are, I want it to check and see if the CurDate (the date on the current
line) is greater than the EarlyDate (BaseDate-90). If both conditions
are met, I want it to change the current Flag to 1 (It won't be 1
because it was tested before and didn't = 1, other wise the macro would
be start testing the other variables on that line).
I want it to contunue moving up one line at at time, changing the flag
until one of the conditions is not met, then go to the line below the
Base line and repeat the process going down, except that the date it
will be testing for is iLateDate (iBaseDate + 90) and it will test for
less than or equal to. (Actually, there is a third test that will be
run, but I figured that if I got the code to get me this far, I can
figure out how to add one more test loop.)
The loops end when the cell is blank, or when the range ends (each
column is a named range), whichever is easiest.
Thanks for the help!

Also there are two possible tests when you check the other rows.

Do you want move up checking the date of every row which has the same ID or
do you want to stop the first time you find a date which is < Base Date - 90?

Similarly when moving down from the base date do you want to check every row
or stop the first time you find a record where the date is out of range?

Rowan


"Rowan" wrote:

Dave

Firstly to answer your questions: the reason I have used Long as the
variable type for testRow is that the maximum positive value for an integer
is 32,767. As you know, newer versions of Excel have 65,536 rows so you could
have enough data in your sheet so that an integer variable would go out of
range causing an error.

I have used the function to trap the known erorr where a flag of 1 is not
found. You could include this in the main routine but normally you would have
seperate error handling code in there to trap other errors that may occur -
some of which you may not have anticipated. It makes it easier to keep track
of this specific error by handling it seperately in the function and also
this allows you to treat this error differently from how you may treat other
errors.

Now to the code. I haven't looked at the changes you have made in detail as
the code I gave you was to deal with option 1 that I mentioned. As we
actually need to do option 2 the result will be quite different. We now want
to find every occurence of "1" so it will be easier to use a loop through
every row of data rather than using the Excel "Find" . This will also take
away the need to have a function to trap the error if there are no 1 flags.

Before I do this though I just want to clear up one more thing. In your data
are all the rows for an ID together. This is the impression that I get. If
this is the case then the code we need should:

Start at first row.
Find first flag "1" and get corresponding ID.
Check every occurence of this ID above the flag. Where date on these lines
is greater than base date - 90 change flag to 1.
Check every occurence of the ID below the flag. Where date on these lines is
<= base date + 90 change flag to 1.
Then move to next ID, search for flag 1 and repeat process.
Repeat until first blank flag found.

Please confirm that these sound like the right steps and then I can look at
the code for you.

Regards
Rowan

"davegb" wrote:

I'm trying to create a macro to mark flags in a worksheet to be used in
another program to analyze data.
The sheet looks something like this:


ID Flag Date
G4 0 02/25/04
H6 1 09/07/03


Actually, there are additional fields between these with text and
numbers not relevant to the macro.
I want the macro to search first in the Flag field until it finds a
"1". I want it to store the ID (iBaseSID) and date (iBaseDate), and
calculate the date 90 days earlier (iEarlyDate). I then want it to
back up one line and compare ID's to see if they are the same. If they
are, I want it to check and see if the CurDate (the date on the current
line) is greater than the EarlyDate (BaseDate-90). If both conditions
are met, I want it to change the current Flag to 1 (It won't be 1
because it was tested before and didn't = 1, other wise the macro would
be start testing the other variables on that line).
I want it to contunue moving up one line at at time, changing the flag
until one of the conditions is not met, then go to the line below the
Base line and repeat the process going down, except that the date it
will be testing for is iLateDate (iBaseDate + 90) and it will test for
less than or equal to. (Actually, there is a third test that will be
run, but I figured that if I got the code to get me this far, I can
figure out how to add one more test loop.)
The loops end when the cell is blank, or when the range ends (each
column is a named range), whichever is easiest.
Thanks for the help!

Rowan,
Thanks for taking the time to explain these things to me. I want to be
able to do this on my own and I can only do that if I understand why
you're doing things as well as what you're doing.
As to your questions.

Before I do this though I just want to clear up one more thing. In
your data
are all the rows for an ID together. This is the impression that I get

Correct. In fact the database has already been sorted by ID and by
Date, so the ID's are together, and within each ID, the dates are in
ascending order (earliest to latest). Once we reach a date less than
dBaseDate-90, or one greater then dBaseDate+90, we can stop looking in
the ID. There won't be any more.
So your description of the code would change to:

Start at first row.
Find first flag "1" and get corresponding ID.
Check to verify the ID is the same, then check to see if the date is
greater than dBaseDate-90. If both criteria are met, set flag on that
row to 1. If it fails either test, move to the cell below the Base row
and start downward.
Move down one row at a time, comparing ID and date. If it meets both
criteria, set flag to 1 and go to the next row down. If it fails, start
searching for the next occurrence of a 1, set that as the new base row,
and work up and then down from there.
Continue until you come to a blank field, then end.

I'm learning as much about how to describe the scenario as I am about
the programming. Wish I could put the flow diagram I did before
starting this on here, would have been much clearer.
Thanks again!

Dave

I have made one more assumption about your data and that is that for each ID
only one record will have a flag of 1 to start with.

With ID in Col A, Flag in Col C and Date in Col E the code is as follows:

Sub TestLoop()

Dim testRow As Long
Dim baseID As String
Dim baseDate, earlyDate, lateDate As Date
Dim testCond As Boolean

Range("C2").Select 'first flag

'loop started to check each value until a blank is found
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 1 Then ' check for 1
testRow = ActiveCell.Row
baseID = ActiveCell.Offset(0, -2).Value
baseDate = ActiveCell.Offset(0, 2).Value
earlyDate = baseDate - 90
lateDate = baseDate + 90

testCond = True
Do While testCond = True ' loop up and check values
ActiveCell.Offset(-1, 0).Select
If ActiveCell.Offset(0, -2).Value = baseID _
And ActiveCell.Offset(0, 2).Value earlyDate Then
ActiveCell.Value = 1 ' changes flag
Else
testCond = False
End If
Loop
Cells(testRow, 3).Select ' moves to testcell
testCond = True
Do While testCond = True ' loop down and check values
ActiveCell.Offset(1, 0).Select
If ActiveCell.Offset(0, -2).Value = baseID _
And ActiveCell.Offset(0, 2).Value <= lateDate Then
ActiveCell.Value = 1 ' changes flag
Else
testCond = False
End If
Loop
Else
' if flag not 1 then moves to next row down
ActiveCell.Offset(1, 0).Select
End If
Loop ' loops back

End Sub

A couple of other pointers. In your first post you mentioned that you had
set each column up as a named range. This is not necessary as you can refer
to columns easily using VBA either by their Name eg A, B, C or by a number
representing their position on the sheet: 1 = col A, 2 = col B etc. You can
see this in the line of code abov: Cells(testRow, 3).Select . This selects
the cell with the address of our test row and column 3 (C).

The easiest way to see what each line of code is doing is to start off by
tiling your windows so that you can see both your excel spreadsheet and the
VB editor on the screen. Then click anywhere in the code and press F8. This
will start the macro and pressing F8 will run one line at a time (stepping
through the code). You can see on the excel screen the result of each line of
code being executed.

If you are interested in learning more about VBA for excel, I started out
with "Excel 2000 Programming for Dummies" by John Walkenbach ISBN
0-7645-0566-1. This is an excellent introduction to VBA.

Good Luck
Rowan




"davegb" wrote:

I'm trying to create a macro to mark flags in a worksheet to be used in
another program to analyze data.
The sheet looks something like this:


ID Flag Date
G4 0 02/25/04
H6 1 09/07/03


Actually, there are additional fields between these with text and
numbers not relevant to the macro.
I want the macro to search first in the Flag field until it finds a
"1". I want it to store the ID (iBaseSID) and date (iBaseDate), and
calculate the date 90 days earlier (iEarlyDate). I then want it to
back up one line and compare ID's to see if they are the same. If they
are, I want it to check and see if the CurDate (the date on the current
line) is greater than the EarlyDate (BaseDate-90). If both conditions
are met, I want it to change the current Flag to 1 (It won't be 1
because it was tested before and didn't = 1, other wise the macro would
be start testing the other variables on that line).
I want it to contunue moving up one line at at time, changing the flag
until one of the conditions is not met, then go to the line below the
Base line and repeat the process going down, except that the date it
will be testing for is iLateDate (iBaseDate + 90) and it will test for
less than or equal to. (Actually, there is a third test that will be
run, but I figured that if I got the code to get me this far, I can
figure out how to add one more test loop.)
The loops end when the cell is blank, or when the range ends (each
column is a named range), whichever is easiest.
Thanks for the help!

Thanks again, Rowan. And thanks for the additional information. As for
the book, I had already ordered, and received when I got home
yesterday, XL 2000 Power Programming with VBA by John Walkenbach. I
debated over getting the "Dummies" book, I know I'm pretty much a
beginner, but was afraid it might not cover some of the stuff I
eventually want to do. I'll see in the next couple of weeks if I made
the right choice.
I have, at least, been "walking" through the code and placing watches &
breaks to figure out what's working and where the problems are. Any
tips in this area are greatly appreciated!

I must admit, I'm impressed! The code you sent worked the first time I
ran it! Another problem solved. Thanks for your patience and your
diligence in helping me get this done. Hopefully, I'll be able to do
more on my own from having seen how you did this.