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!